Error with dynamic SQL query variable declaration

Question

I ran the following query in SQL

declare @h nvarchar(max)
declare @i int
set @i =1
declare @two nvarchar(max)
select @h = 'set @two = (select word from #LocalTempTable where Idcolumn =' + cast(@i as nvarchar(max)) +')'
exec(@h)
print @two

I got the following error

Msg 137, Level 15, State 1, Line 1
Must declare the scalar variable "@two".

Why is this happening?

Its because the scope of dynamic sql is different than the batch or proc that the variable is declared. Use sp_executeSQL instead, it accepts parameters. You're still going to have a problem referencing #LocalTempTable — Conrad Frix
– Conrad Frix, Commented Jan 9, 2014 at 20:48
Thanks Conrad. I changed the exec(@h) to exec sp_executeSQL @h. Still getting the exact same error. Did I make the right change? — user3171919
– user3171919, Commented Jan 9, 2014 at 20:53

kba · Accepted Answer · 2014-01-09 21:20:19Z

3

Here is the corrected one. And here is the sqlfiddle.

declare @h nvarchar(max)
declare @i int
set @i =1
declare @two nvarchar(max)
select @h = 'select @to = word from #LocalTempTable where Idcolumn =' + cast(@i as nvarchar(max))
exec sp_executesql @h, N'@to nvarchar(max) output', @to=@two output
print @two

edited Jan 9, 2014 at 21:20

kba

19.5k6 gold badges65 silver badges90 bronze badges

answered Jan 9, 2014 at 20:55

Consult Yarla

1,15010 silver badges22 bronze badges

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

2 Comments

user3171919 Over a year ago

This was beautiful thank you, I will probably have on more question for you Consult Yarla. Thanks!

Consult Yarla Over a year ago

Sure, shoot the question is SO, if not me, someone else can also provide answer.-- all the best.

Hart CO · Accepted Answer · 2014-01-09 20:53:08Z

2

You've got an issue with variable scope, @two inside your @h variable is not declared.

You can declare it inside of your @h variable:

DECLARE @h nvarchar(max)
       ,@i INT = 1
SELECT @h = 'declare @two nvarchar(max) set @two = (select ''dog' + CAST(@i as nvarchar(max)) +''')'
EXEC(@h)

You will have a scope issue with the #temp table still, and declaring it inside makes it unavailable outside, so not much point to it.

answered Jan 9, 2014 at 20:53

Hart CO

34.9k6 gold badges52 silver badges66 bronze badges

Collectives™ on Stack Overflow

Error with dynamic SQL query variable declaration

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related