M3How M3How - 4 days ago 5
SQL Question

variable for query string

As higher in hierarchy forum users suggested Im postig my problem as a new question related to this one: declare variable for query string.

Im using dynamic query here because I want to be able to use variables (I need it for my experiment involving parameter sniffing). My query looks like that:

DECLARE @i NVARCHAR(10)
SET @i = '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
...'
EXECUTE (@SQL)


When I want to execute my query Im getting error like:

'Msg 137, Level 15, State 2, Line 28
Must declare the scalar variable "@i".'


When I moved declaration of variable @i into the @sql then it sorta works. Still I don't think It's what I wanted. Am I doing something wrong or it has to look like this?:

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

DECLARE @i NVARCHAR(10)
SET @i = 'POL'

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
...'
EXECUTE (@SQL)


Are there any mistakes in my code so Im getting MSG137 error or it's just impossible task that I want to do here.

I don't want to create a stored procedure from this query. I want to be able to use variables but without relying on stored procedure.

I apologize admins/forum users for problems involving my earlier question in hyperlinked question.

Answer

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 @i NVARCHAR(10)
SET @i = '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
...'
EXECUTE (@SQL)
Comments