如何将EXEC(@SQL)的执行结果保存到变量中用于UPDATE语句

使用SQL Server 2018，现有代码通过动态SQL生成XML结果并直接执行输出，需要将该XML结果保存到变量中以便后续UPDATE语句使用：

--Using SQL SERVER 2018
DECLARE
@MasterID   NVARCHAR(30), 
@INPUTXML   XML 

SET @MasterID = 't_Product'

BEGIN
    SET NOCOUNT ON
    DECLARE @SharedOn   DateTime,
            @TypeID     XML ,
            @SQL        NVARCHAR(500),
            @ErrMsg     NVARCHAR(200)


    CREATE TABLE #TypeID
    (
        ID  NVARCHAR(50)
    )

    SELECT  @SharedOn   = SharedOn,
            @TypeID     = TypeID
    FROM    t_MasterDataShareControl 
    WHERE   MasterID    = @MasterID


    INSERT INTO #TypeID
    (   
        ID
    )
    SELECT          
            NULLIF(TypeID.x.value('ID[1]','nvarchar(250)'),'')
    FROM    @TypeID.nodes('/dt_TypeID/dt_ID') AS TypeID(x)

    

    IF @MasterID IN ('t_Product','t_WFAdvRule') AND @TypeID IS NULL
    BEGIN
        SET @SQL =  'SELECT * 
                     FROM ' + @MasterID + 
                    ' WHERE ISNULL(ModifiedOn,CreatedOn)'+ '>' + concat(char(39),@SharedOn,char(39))
    END
    IF @MasterID IN ('t_SystemCodeDetail','t_UserCodeDetail','t_BankUserCode','t_BranchUserCode' ) AND @TypeID IS NOT NULL
    BEGIN
    SET @SQL =  'SELECT * 
                 FROM ' + @MasterID +
                ' WHERE ISNULL(ModifiedOn,CreatedOn) >' + concat(char(39),@SharedOn,char(39)) +
                'AND    ID IN ( SELECT  * 
                                FROM    #TypeID )'
    END

    SET @SQL = @SQL + ' FOR XML PATH('+ concat(char(39),@MasterID,char(39)) + '),ROOT(''InputXML'')'

    EXEC (@SQL) -- 这里直接输出，需要保存到变量



    ENDSTMT:

    DROP TABLE #TypeID
    SET NOCOUNT OFF
END

之前尝试的方法未成功：

• 使用sp_executesql但指定NVARCHAR(MAX)类型输出参数，无法正确保存XML结果
• 尝试插入临时表时收到错误：

FOR XML子句不允许在INSERT语句中使用。

解决方案 #

核心方法：用sp_executesql配合XML类型输出参数 #

动态SQL生成的是XML结果，需直接用XML类型变量接收，而非字符串类型。同时优化动态SQL拼接的安全性和正确性：

修改后的完整代码

--Using SQL SERVER 2018
DECLARE
@MasterID   NVARCHAR(30), 
@INPUTXML   XML 

SET @MasterID = 't_Product'

BEGIN
    SET NOCOUNT ON
    DECLARE @SharedOn   DateTime,
            @TypeID     XML ,
            @SQL        NVARCHAR(MAX), -- 扩大长度避免截断
            @ErrMsg     NVARCHAR(200)


    CREATE TABLE #TypeID
    (
        ID  NVARCHAR(50)
    )

    SELECT  @SharedOn   = SharedOn,
            @TypeID     = TypeID
    FROM    t_MasterDataShareControl 
    WHERE   MasterID    = @MasterID


    INSERT INTO #TypeID
    (   
        ID
    )
    SELECT          
            NULLIF(TypeID.x.value('ID[1]','nvarchar(250)'),'')
    FROM    @TypeID.nodes('/dt_TypeID/dt_ID') AS TypeID(x)

    

    -- 重构动态SQL，使用参数化避免注入和格式问题
    IF @MasterID IN ('t_Product','t_WFAdvRule') AND @TypeID IS NULL
    BEGIN
        SET @SQL =  'SELECT * 
                     FROM ' + QUOTENAME(@MasterID) + -- 用QUOTENAME处理表名，防止注入
                    ' WHERE ISNULL(ModifiedOn,CreatedOn) > @SharedOn';
    END
    IF @MasterID IN ('t_SystemCodeDetail','t_UserCodeDetail','t_BankUserCode','t_BranchUserCode' ) AND @TypeID IS NOT NULL
    BEGIN
    SET @SQL =  'SELECT * 
                 FROM ' + QUOTENAME(@MasterID) +
                ' WHERE ISNULL(ModifiedOn,CreatedOn) > @SharedOn' +
                ' AND ID IN ( SELECT  * FROM #TypeID )';
    END

    -- 添加FOR XML子句
    SET @SQL = @SQL + ' FOR XML PATH(' + QUOTENAME(@MasterID, '''') + '),ROOT(''InputXML'')';

    -- 调整为将结果赋值给输出参数的语句
    SET @SQL = 'SELECT @ResultXML = (' + @SQL + ')';

    -- 使用sp_executesql执行，传入输入参数和输出参数
    EXEC sp_executesql 
        @SQL,
        N'@SharedOn DATETIME, @ResultXML XML OUTPUT', -- 定义参数类型
        @SharedOn = @SharedOn, -- 传入日期参数
        @ResultXML = @INPUTXML OUTPUT; -- 绑定输出变量

    -- 此时@INPUTXML已保存XML结果，可用于后续UPDATE语句
    SELECT @INPUTXML; -- 验证结果


    ENDSTMT:

    DROP TABLE #TypeID
    SET NOCOUNT OFF
END

关键说明 #

1. 参数类型匹配：用XML类型作为输出参数，直接匹配FOR XML的结果，避免隐式转换导致的问题。
2. 安全拼接：用QUOTENAME处理表名和XML节点名，防止SQL注入；将@SharedOn作为参数传入动态SQL，避免日期格式错误和注入风险。
3. 临时表访问：本地临时表#TypeID在当前会话内创建，sp_executesql作为子批处理可以直接访问，无需改为全局临时表。

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