如何通过动态SQL跨服务器调用存储过程并获取多输出值与返回值

你现有代码存在3个核心问题，会导致无法获取目标值：

• 用普通EXEC ()执行动态SQL时，动态SQL内部的参数作用域和外部完全隔离，无法跨作用域回传值
• 调用远程存储过程时未给输出参数标记OUTPUT，参数值不会回传
• 未定义变量捕获存储过程的RETURN返回值，且四部分链接对象名顺序写反（原代码写为服务器.dbo.数据库.存储过程，正确格式为[链接服务器名].[数据库名].[架构名].[存储过程名]）

核心实现逻辑 #

• 改用sp_executesql执行动态SQL：该系统存储过程支持参数化绑定，可明确指定输入、输出参数，既能规避SQL注入风险，也能跨动态SQL作用域获取回传值
• 单独定义INT类型变量，通过@变量 = 存储过程名的语法捕获远程存储过程的RETURN返回值
• 所有输出参数在动态SQL的参数定义、执行调用处都必须显式加OUTPUT标记
• 拼接链接服务器名时用QUOTENAME()包裹，避免特殊字符导致语法错误

修正后完整代码 #

CREATE PROCEDURE [MYPROCEDURE](
    @PARAM1 INT,
    @PARAM2 NVARCHAR(250) OUTPUT, -- 原定义缺少OUTPUT标记，无法将值回传给上层调用方
    @PARAM3 INT OUTPUT, -- 同上，必须加OUTPUT标记
    @SERVER_NAME NVARCHAR(MAX),
    @PROC_RETURN INT OUTPUT -- 如需把远程过程的RETURN值透传给MYPROCEDURE的调用方，可加该输出参数
)
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @SQLQUERY NVARCHAR(MAX)
    -- 定义内部变量接收动态SQL回传的值
    DECLARE @INNER_P2 NVARCHAR(250)
    DECLARE @INNER_P3 INT
    DECLARE @INNER_RET INT

    -- 拼接动态SQL，注意四部分名顺序：服务器.数据库.dbo.存储过程，此处数据库名按实际业务替换为你的远程库名
    SET @SQLQUERY = N'
        SELECT @INNER_RET = [SERVER_PLACEHOLDER].[你的远程数据库名].[dbo].[OTHERPROCEDURE]
            @PARAM1 = @IN_P1,
            @PARAM2 = @IN_P2 OUTPUT,
            @PARAM3 = @IN_P3 OUTPUT
    '
    -- 安全替换传入的服务器名，QUOTENAME会自动加方括号规避特殊字符问题
    SET @SQLQUERY = REPLACE(@SQLQUERY, '[SERVER_PLACEHOLDER]', QUOTENAME(@SERVER_NAME))

    -- 执行动态SQL，绑定所有入参、出参
    EXEC sp_executesql
        @stmt = @SQLQUERY,
        @params = N'
            @INNER_RET INT OUTPUT,
            @IN_P1 INT,
            @IN_P2 NVARCHAR(250) OUTPUT,
            @IN_P3 INT OUTPUT
        ',
        @INNER_RET = @INNER_RET OUTPUT,
        @IN_P1 = @PARAM1,
        @IN_P2 = @INNER_P2 OUTPUT,
        @IN_P3 = @INNER_P3 OUTPUT

    -- 将拿到的回传值赋值给存储过程输出参数，供上层调用使用
    SET @PARAM2 = @INNER_P2
    SET @PARAM3 = @INNER_P3
    SET @PROC_RETURN = @INNER_RET

    -- 如果需要MYPROCEDURE本身直接返回远程过程的返回值，可打开下面的注释
    -- RETURN @INNER_RET
END

必要配置&注意事项 #

• 调用前必须开启对应链接服务器的RPC OUT选项，否则调用远程存储过程会报错，执行以下命令开启即可：

  EXEC sp_serveroption @server='你的链接服务器名', @optname='rpc out', @optvalue='true'
  

• 建议增加校验逻辑，判断传入的@SERVER_NAME是否为当前实例已配置的合法链接服务器，避免恶意拼接注入风险
• 如果远程存储过程走到无显式RETURN的分支，捕获到的返回值为默认值0，可根据业务逻辑自行判断该场景
• 代码中你的远程数据库名占位符请替换为OTHERPROCEDURE实际所在的数据库名称，不要和架构名dbo写反顺序

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