SQL Server如何在EXECUTE命令内给外部参数PRGMREFID赋值

• 原代码拼接动态SQL时，@PRGMREFID初始化默认值为NULL，SQL中NULL与任意字符串拼接的结果都是NULL，最终生成的@C实际为空内容，执行后自然不会给变量赋值，所以查询结果始终为NULL。
• 普通EXECUTE()执行动态SQL的作用域是独立的，和外层会话的变量不互通，即使你在动态SQL中硬写变量名，也属于动态SQL内部的局部变量，外层声明的变量无法读取到内部赋值结果。

使用sp_executesql系统存储过程替代普通EXECUTE()，它支持绑定输入/输出参数，可以将外层变量以输出参数的形式传入动态SQL，执行后即可直接拿到赋值结果，修改后的代码如下：

DECLARE @C NVARCHAR(MAX) -- sp_executesql要求动态语句必须为NVARCHAR类型
DECLARE @PRGMREFID VARCHAR(10)

IF 1=1
-- 动态SQL内用占位符@OutPRGMREFID承载输出值
SET @C = N'set @OutPRGMREFID = ''X'''
ELSE
SET @C = N''

IF @C <> '' -- 避免执行空语句抛出异常
-- 调用sp_executesql，依次传入：动态语句、参数定义、参数映射关系
EXEC sp_executesql 
    @C,
    N'@OutPRGMREFID VARCHAR(10) OUTPUT', -- 定义输出参数的类型和属性
    @OutPRGMREFID = @PRGMREFID OUTPUT -- 将外层变量绑定到动态SQL的输出参数

SELECT @PRGMREFID -- 此时可正确读取到赋值结果X

注意事项 #

• sp_executesql要求传入的动态SQL语句必须为NVARCHAR类型，因此声明@C时要改为NVARCHAR(MAX)，赋值时字符串前加N前缀。
• 参数定义部分要和动态SQL内用到的参数类型完全一致，需要返回值的参数要加OUTPUT关键字，绑定外层变量时同样要加OUTPUT才能完成值的回传。

内容的提问来源于stack exchange，提问作者Peeyush Bansal