sql – variable for query string

sql – variable for query string

You need to break the string. The value @i is not available in the scope the string is executed in, so you need to make it part of the string.
Like so:

DECLARE @tmp NVARCHAR(10)
SET @tmp = POL

DECLARE @sql VARCHAR(MAX)
SET @sql = SELECT

a.something1,
b.something2,
c.something3

FROM
aaa a WITH(NOLOCK)
LEFT JOIN bbb b WITH(NOLOCK) ON....
LEFT JOIN ccc c WITH(NOLOCK) ON....

WHERE
a.somethingelse = aa
AND
((a.entirelysomethingelse=aaa) OR (a.entirelysomethingelse=aaaa))
AND
b.anotherdifferentsomething != 41
AND 
c.yetanotherdifferentthing LIKE(%@%)
AND
c.datafromvariablewannabe = (@i)
GROUP BY
...
ORDER BY
...
OPTION(OPTIMIZE FOR(@i =  + @tmp + ))
EXEC SP_EXECUTESQL @SQL, N@i NVARCHAR(255), @i = @tmp

The working solution with optimize for @variable would look like that:

DECLARE @i NVARCHAR(255)
declare @tmp nvarchar(255)
SET @tmp = POL

DECLARE @SQL nVARCHAR(MAX)
SET @sql = SELECT

a.something1,
b.something2,
c.something3

FROM
aaa a WITH(NOLOCK)
LEFT JOIN bbb b WITH(NOLOCK) ON....
LEFT JOIN ccc c WITH(NOLOCK) ON....

WHERE
a.somethingelse = aa
AND
((a.entirelysomethingelse=aaa) 
OR 
(a.entirelysomethingelse=aaaa))
AND
b.anotherdifferentsomething != 41
AND 
c.yetanotherdifferentthing LIKE(%@%)
AND
c.datafromvariablewannabe = (@i)

GROUP BY
...
ORDER BY
...

OPTION(OPTIMIZE FOR(@i =  + @tmp + ))

EXECUTE SP_EXECUTESQL @SQL, N@i NVARCHAR(255), @i = @tmp

It didnt worked without declaring variable @temp. After that I encountered a problem with Exec. I couldnt use SP_EXECUTESQL as @mortb suggested. I had to use EXECUTE before SP_EXECUTESQL and I found some information related to next error in that post Why do I get Procedure expects parameter @statement of type ntext/nchar/nvarchar. when I try to use sp_executesql?. I had to change type for a query variable and get ridd of brackets.

Still I would like to know why I had to create new variable and Itd be helpful to know a little bit more about this part of code:

OPTION(OPTIMIZE FOR(@i =  + @tmp + ))

EXECUTE SP_EXECUTESQL @SQL, N@i NVARCHAR(255), @i = @tmp

It works now but Id like to know why and how?

sql – variable for query string

Leave a Reply

Your email address will not be published. Required fields are marked *