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...