Posts

Showing posts from November, 2015

Dynamic Query in SQL Server

Sometimes we need to modify query based on different condition , scenarios etc. To avoid such long length and repeated code , we build dynamic query. Here output same as normal query execution. Syntax - declare @var1 varchar(max) or nvarchar(max) Select @var1 = N'<query to be written>' Exec <default sp name> @var1 Following points need to consider for writing dynamic query : 1.  Combination of methods and declaration   Suppose we are declare in following way as - declare @var varchar(max) select @var = N'select * from datUsers' select @var execute sp_executesql @var Here error occur as Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'. Follow below combination based on variable datatypes 1.  using VARCHAR - declare @var varchar(max) select @var = N'select * from datUsers' select @var execute(@var) OUTPUT - (1 row(s) affected) 2.  using NVARCHAR - declare @var1 nvarchar(max) sel