Dynamic query runs directly but not through variable, what could be the reason?
- by waheed
Here is my scenario, I'm creating a dynamic query using a select statement which uses functions to generate the query. I am storing it into a variable and running it using exec. i.e.
declare @dsql nvarchar(max)
set @dsql = ''
select @dsql = @dsql + dbo.getDynmicQuery(column1, column2)
from Table1
exec(@dsql)
Now it produces the many errors in this scenario, like 'Incorrect syntax near ','' and 'Case expressions may only be nested to level 10.'
But if i take the text from @dsql and assign it a variable manually like:
declare @dsql nvarchar(max)
set @dsql = ''
set @dsql = N'<Dynamic query text>'
exec(@dsql)
it runs and generates the result, what could be the reason for that ??
Thanks..
