sql - 为什么在动态 SQL 中出现“必须声明标量变量”错误?
问题描述
我在为变量设置值时尝试使用动态 SQL,但它不起作用。但是,相同的语句在常规 SQL 中确实有效。这是代码:
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')
这就是它返回的内容:
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
我究竟做错了什么?
解决方案
您需要在动态 SQL 批处理中绑定一个输出参数并将您的局部变量分配给该参数。像这样:
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')
推荐阅读
- c# - 如何访问数据源是存储过程的值
- android-layout - layout_constraintStart_toEndOf 和 layout_constraintLeft_toRightOf 有什么区别
- api - 无法检索播放列表曲目列表 Apple Music API
- macos - 如何使用 AppleScript 获取图像(jpeg、raw)的捕获日期时间?
- mpi - 使用带有 set -e 选项的 mpivars.sh
- jhipster - 如何卸载生成器-jhipster
- angular - 仅以一种方式绑定
- xml - 如何在 XML 中使用替换字符 (^Z)?
- javascript - onclick删除行后如何在模式中显示行内容
- c# - 无法通过 id 找到元素 - selenium