CODEWITHSUNDEEP
sqlsql-servert-sqldynamic-sql
cashonly
cashonly

Reputation: 119

Why do I get a "must declare scalar variable" error in dynamic SQL?

I am trying to use Dynamic SQL when setting a value to a variable, but it doesn't work. However, the same statement does work in regular SQL. This is the code:

DECLARE @sqlcmd nchar(1024);
DECLARE @DBName nchar(30) = 'DB_1016a'
DECLARE @UserKey int = 0;
DECLARE @UserID nchar(30) = 'DBCLIENT\StudentA'
set @sqlcmd = 'set @UserKey = (SELECT [Key] from ' + rtrim(ltrim(@DBName)) + '.dbo.userlist where ID = ''' + rtrim(ltrim(@UserID)) + ''')'
print(@sqlcmd)
exec(@sqlcmd)
print('stuff1')
print('['+rtrim(ltrim(cast(@UserKey as nchar(4))))+']')
print('stuff2')

And this is what it returns:

set @UserKey = (SELECT [Key] from DB_1016a.dbo.userlist where ID = 'DBCLIENT\StudentA')                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         
*Msg 137, Level 15, State 1, Line 30
Must declare the scalar variable "@UserKey".*    
stuff1
[0]
stuff2

What am I doing wrong?

Upvotes: 0

Views: 820

Answers (2)

David Browne - Microsoft
David Browne - Microsoft

Reputation: 88996

You need to bind an output parameter in the dynamic SQL batch and assign your local variable to the parameter. Like this:

DECLARE @sqlcmd nchar(1024);
DECLARE @DBName nchar(30) = 'DB_1016a'
DECLARE @UserKey int;
DECLARE @UserID nchar(30) = 'DBCLIENT\StudentA'
set @sqlcmd = 'set @UserKey = (SELECT [Key] from ' + rtrim(ltrim(@DBName)) + '.dbo.userlist where ID = ''' + rtrim(ltrim(@UserID)) + ''')'
print(@sqlcmd)
exec sp_executesql @sqlcmd, N'@UserKey int out', @UserKey = @UserKey output
print('stuff1')
print('['+rtrim(ltrim(cast(@UserKey as nchar(4))))+']')
print('stuff2')

Upvotes: 4

squillman
squillman

Reputation: 13641

You're dealing with a scope issue. The statement contained in @sqlcmd is in a different execution scope than that where you declare @UserKey when you run it with exec.

Upvotes: 1

Related Questions