为什么在动态 SQL 中出现"must declare scalar variable"错误？

Question

我试图在为变量设置值时使用动态SQL，但它不起作用。但是，同样的语句在常规SQL中也可以使用。这是代码：

DECLARE @sqlcmd nchar(1024);
DECLARE @DBName nchar(30) = 'DB_1016a'
DECLARE @UserKey int = 0;
DECLARE @UserID nchar(30) = 'DBCLIENTStudentA'
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')

这就是它的回报：

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

我做错了什么？

Answer 1

您需要在动态SQL批处理中绑定一个输出参数，并将本地变量分配给该参数。像这样：

DECLARE @sqlcmd nchar(1024);
DECLARE @DBName nchar(30) = 'DB_1016a'
DECLARE @UserKey int;
DECLARE @UserID nchar(30) = 'DBCLIENTStudentA'
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')

Answer 2

您正在处理一个范围问题。@sqlcmd中包含的语句与使用exec运行时声明@UserKey的语句所处的执行范围不同。