0

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.

Community
  • 1
  • 1
Grumpy Guard
  • 59
  • 1
  • 1
  • 14
  • 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 Nov 29 '16 at 10:02
  • 2
    variables are scoped to a batch and exec runs in seperate batch,so you have to include it in same @sql – TheGameiswar Nov 29 '16 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 Nov 29 '16 at 10:17
  • 1
    Off-topic but worth a read: [the downside of using NoLock](https://blogs.sentryone.com/aaronbertrand/bad-habits-nolock-everywhere/). 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](http://stackoverflow.com/questions/32516139/how-do-i-pass-input-parameters-to-sp-executesql) for an alternative method on passing variables into dynamical SQL. – David Rushton Nov 29 '16 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 Nov 29 '16 at 10:31

2 Answers2

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
mortb
  • 9,361
  • 3
  • 26
  • 44
  • 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 Nov 29 '16 at 10:14
  • 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. – Grumpy Guard Nov 29 '16 at 11:18
  • 1
    After the ORDER BY Ref: https://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 Nov 29 '16 at 13:23
  • I tried: `option (optimize for ('+ @i +' = ''POL'')` and `option (optimize for (@i = 'POL'))` and `option (optimize for ('+@i+' = '''POL''')` but it's not working. It's working within 2nd query from my question but I can't get it right within query from your answer... Im getting `Msg 102, Level 15, State 1, Line 39 Incorrect syntax near 'POL'.` – Grumpy Guard Nov 29 '16 at 13:29
  • 1
    (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 Nov 29 '16 at 13:34
  • If I understood correctly it should look like that: `... ORDER BY ... option(optimize for (' + @i + '))` but it's not working. Im still getting the same error. – Grumpy Guard Nov 29 '16 at 13:38
  • 1
    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 – mortb Nov 29 '16 at 14:00
  • Im sorry but Im still confused. Could you post it as an answer and tell me some more how to use it? It seems that I have to make another variable and I don't really know why and where? Sorry for the trouble but Im still an unexperienced trainee. I tried to use the part of the code that you fixed but I got two more error mesages: `Msg 137, Level 15, State 1, Line 2 Must declare the scalar variable "@tmp". Msg 137, Level 15, State 2, Line 44 Must declare the scalar variable "@tmp".` – Grumpy Guard Nov 29 '16 at 14:09
  • Wouldn't `OPTIMIZE FOR UNKNOWN` achieve the same? – Andriy M Dec 06 '16 at 09:39
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?

Community
  • 1
  • 1
Grumpy Guard
  • 59
  • 1
  • 1
  • 14
  • 1
    `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 Nov 29 '16 at 16:04
  • 1
    Give it some time and you will understand :) – mortb Nov 29 '16 at 16:05