Dynamic query runs directly but not through variable, what could be the reason?

Question

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

Answer 1

I have resolved it, actually inside getDynmicQuery(), i am using few .Net functions for few specific parts of the query.

Now the problem was that, in the query text there was single quotes as well and i was generating them in a standard SQL way, i.e. two single quotes wherever i needed one single quote in the query text. It was OK, for UDF, but for .Net function that created problem.

Because the query generated by .Net function was simple text and i just concatenated it with another variable to get one complete query so escaping single quote inside .Net function was not required.

whenever i used select it was escaping so query was ok, same was the case with SELECT @dsql AS [processing-instruction(x)] FOR XML PATH(''), print() had helped me to figure this out, since it didn't escaped anything, but problem with it was, that truncated my query, so i have try a specific shorter version of query for testing..

Edit, A little more detail:

Since i'm generating scripts in two steps,

1. Inside a UDF
2. .Net function

whole issue is of single quotes, Initially inside UDF and .Net function where i needed one single quotes in generated query i used two single quotes in dynamic query text(one for escaping).

The correction that i had done, is that in .Net function, where i needed single quote in generated query, i am now using single quote, no escaping.

Now whenever in old scenario, i used select to select the dynamic query text, single quote escaping was done, so i didn't received the exact query, what print did is that, it printed without doing any escaping, which made it possible to see the error.

Thanks Everybody..

Answer 2

Use:

PRINT(@dsql) 

...prior to the EXEC call (should be using EXEC sp_executesql instead btw), and test the output from the print to see where the syntax error is coming from.