variable for query string

Question

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.

what does this mean When I moved declaration of variable @i into the @sql then it sorta works. Still I don't think It's what I wanted — TheGameiswar
– TheGameiswar, Commented Nov 29, 2016 at 10:02
variables are scoped to a batch and exec runs in seperate batch,so you have to include it in same @sql — TheGameiswar
– TheGameiswar, Commented Nov 29, 2016 at 10:03
I meant that I got good result but the method of getting those results was not the one that I was searching for. I didn't know that i have to "break" the query string (as @mortb sugessted) so my variable would be able to be "loaded?" in there. — Grumpy Guard
– Grumpy Guard, Commented Nov 29, 2016 at 10:17
Off-topic but worth a read: the downside of using NoLock. I'm sure you have your reasons but it is worth highlighting that NoLock can return duplicates, deleted records, and other stuff you most likely don't want in a production query. See this question for an alternative method on passing variables into dynamical SQL. — David Rushton
– David Rushton, Commented Nov 29, 2016 at 10:27
I know that nolock have it's downsides but using it here is an order from my chief and in this case it won't do any harm. Still usefull comment though. — Grumpy Guard
– Grumpy Guard, Commented Nov 29, 2016 at 10:31

mortb · Accepted Answer · 2016-11-29 15:52:35Z

2

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

edited Nov 29, 2016 at 15:52

answered Nov 29, 2016 at 10:04

mortb

9,9213 gold badges30 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

Grumpy Guard Over a year ago

This is the answer that I was searching for. Still I needed to add quotation marks to @i variable. So It has to look like that: DECLARE @i nvARCHAR(10) SET @i ='''POL'''

Grumpy Guard Over a year ago

There is one more thing. If I'd want to add here option (optimize for @i) where or how should I do that? In this particular case.

mortb Over a year ago

After the ORDER BY Ref: msdn.microsoft.com/en-us/library/ms181714.aspx It is specific to one query statement (you should put it last in you SELECT statement)

mortb Over a year ago

(optimize for ('+ @i +') Explanation: you have already assigned the value 'POL' to the variable @i. ' + @i + ' will assign the value of @i into your string

mortb Over a year ago

Oh, sorry. I edited the SQL statement. You should probably use SP_EXECUTESQL instead of EXEC. Then you can input real parameters to your sql statement

|

Community · Accepted Answer · 2017-05-23 11:55:16Z

1

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 didn't worked without declaring variable @temp. After that I encountered a problem with Exec. I couldn't 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 It'd 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 I'd like to know why and how?

edited May 23, 2017 at 11:55

CommunityBot

11 silver badge

answered Nov 29, 2016 at 15:02

Grumpy Guard

591 gold badge1 silver badge14 bronze badges

2 Comments

mortb Over a year ago

SP_EXECUTESQL is a built in stored procedure that takes a string of SQL and runs it with the specified parameters. It is confusing in the beginning when you make sql from a string. The part N'@i NVARCHAR(255)', @i = @tmp tells the stored procedure that there will be a parameter in the sql text that is named @i and that @i (when the text executes) should have the same value @tmp has "outside" the text. The part OPTION(OPTIMIZE FOR(@i = ''' + @tmp + '''))' means the text will contain the value of @tmp (it is no longer a variable when the text is fed into SP_EXECUTESQL).

mortb Over a year ago

Give it some time and you will understand :)

Collectives™ on Stack Overflow

variable for query string

2 Answers 2

9 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

9 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related