SQL Server中如何将动态SQL查询的输出存入已声明的变量

你可以通过 sp_executesql 自带的输出参数绑定特性实现结果赋值，无需额外临时表存储，同时还可以对非动态拼接的参数做参数化处理，避免SQL注入风险，完整调整代码如下：

declare @EM_NUMBER nvarchar(100)
declare @SQL nvarchar(max)

-- 构造动态SQL时增加输出参数占位，非动态字段全部改为参数化传入
set @SQL=N'select @EM_NUMBER_OUT = vendor.VD_TABLE_KEY_VALUE 
          from [HS_EM_VENDOR_DATA_MAP] as vendor 
          inner join [HS_HR_EMPLOYEE] as emp on vendor.VD_TABLE_KEY_VALUE = emp.EMP_NUMBER
          where vendor.VD_TABLE_NAME = ''HS_HR_EMPLOYEE''
            and vendor.VD_TENANT_UNIQUE_ID = @VD_TENANT_UNIQUE_ID
            and emp.HIE_CODE_' + @DEF_LEVEL + ' = @HIE_CODE
            and vendor.VD_TABLE_REF_VALUE = @XeroEmpNumber'

-- 执行动态SQL，绑定输入输出参数
EXEC sp_executesql 
    @SQL,
    -- 定义所有参数的类型，输出参数需加OUTPUT标记
    -- 请把下方参数类型替换为你实际业务中的字段类型
    N'@VD_TENANT_UNIQUE_ID nvarchar(100), 
      @HIE_CODE nvarchar(100), 
      @XeroEmpNumber nvarchar(100), 
      @EM_NUMBER_OUT nvarchar(100) OUTPUT',
    -- 传入输入参数值
    @VD_TENANT_UNIQUE_ID = @VD_TENANT_UNIQUE_ID,
    @HIE_CODE = @HIE_CODE,
    @XeroEmpNumber = @XeroEmpNumber,
    -- 绑定输出参数到你声明的变量
    @EM_NUMBER_OUT = @EM_NUMBER OUTPUT

-- 后续可直接使用@EM_NUMBER变量
SELECT @EM_NUMBER

注意事项 #

• 如果你的查询可能返回多条结果，只会把最后一条记录的VD_TABLE_KEY_VALUE赋值给@EM_NUMBER，如果确定查询仅返回唯一结果，建议在SELECT后加TOP 1避免异常
• 只有动态拼接的列名/表名（比如示例中的@DEF_LEVEL用于拼接列名）需要直接拼接SQL，其他值类参数都建议用参数化传入，无需处理单引号转义问题，同时避免SQL注入风险

内容的提问来源于stack exchange，提问作者Asanka Yaparathna