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)
select @var1 = N'select * from datCustomerGroups'
select @var1
execute sp_executesql @var1


OUTPUT - (1 row(s) affected)