CODEWITHSUNDEEP
sqlsql-servervariables
user3171919
user3171919

Reputation: 99

Error with dynamic SQL query variable declaration

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?

Upvotes: 0

Views: 430

Answers (2)

Consult Yarla
Consult Yarla

Reputation: 1150

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

Upvotes: 3

Hart CO
Hart CO

Reputation: 34774

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.

Upvotes: 2

Related Questions