SQL Server中如何在存储过程中调用另一存储过程返回的多个结果集

你可以通过回环链接服务器+OPENQUERY的方案实现需求，全程不需要修改Old存储过程的原有逻辑，也不需要合并结果集：

前置准备（仅需执行一次） #

首先配置指向本地实例的回环链接服务器：

-- 启用高级选项
sp_configure 'show advanced options', 1;
RECONFIGURE;
sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;
GO

-- 创建回环链接服务器
EXEC sp_addlinkedserver @server = N'Loopback', @srvproduct=N'', @provider=N'SQLNCLI', @datasrc=@@SERVERNAME;
EXEC sp_serveroption @server=N'Loopback', @optname=N'rpc out', @optvalue=N'true';
EXEC sp_serveroption @server=N'Loopback', @optname=N'data access', @optvalue=N'true';
GO

备注：WITH RESULT SETS语法需要SQL Server 2012及以上版本支持，如果是更低版本，需要保证OPENQUERY查询的字段顺序和类型和结果集完全匹配即可。

New存储过程编写逻辑 #

在New存储过程中声明3个临时表分别匹配Old返回的3个结果集结构，再通过OPENQUERY分别捕获对应结果集：

CREATE OR ALTER PROCEDURE New
AS
BEGIN
    -- 声明3个临时表，结构和Old返回的三个结果集完全对应
    CREATE TABLE #Result1 (XmlData XML);
    CREATE TABLE #Result2 (XmlData XML);
    CREATE TABLE #Result3 (DateValue DATE); -- 实际类型按你第三张表的字段调整

    -- 捕获第一个结果集
    INSERT INTO #Result1(XmlData)
    SELECT * FROM OPENQUERY(Loopback, 'EXEC YourDatabaseName.dbo.Old; WITH RESULT SETS ((XmlData XML))');

    -- 捕获第二个结果集
    INSERT INTO #Result2(XmlData)
    SELECT * FROM OPENQUERY(Loopback, 'EXEC YourDatabaseName.dbo.Old; WITH RESULT SETS ((), (XmlData XML))');

    -- 捕获第三个结果集
    INSERT INTO #Result3(DateValue)
    SELECT * FROM OPENQUERY(Loopback, 'EXEC YourDatabaseName.dbo.Old; WITH RESULT SETS ((), (), (DateValue DATE))');

    -- 后续就可以直接使用三个临时表中的数据做业务逻辑
END
GO

注意将代码中的YourDatabaseName替换为你实际的数据库名称，临时表的字段结构要和Old存储过程返回的对应结果集完全一致。

备选方案（可微调Old存储过程但不合并结果集） #

如果你可以对Old存储过程做少量调整，不需要合并结果集，只需要增加两个XML输出参数即可，性能比分布式查询方案更好：

1. 修改Old存储过程：

CREATE OR ALTER PROCEDURE Old
    @OutXml1 XML OUTPUT,
    @OutXml2 XML OUTPUT
AS
BEGIN
    -- 原有查询逻辑不变，额外把前两个单行XML赋值给输出参数
    SELECT @OutXml1 = XmlField FROM Table1;
    SELECT @OutXml2 = XmlField FROM Table2;
    SELECT * FROM Table1;
    SELECT * FROM Table2;
    SELECT * FROM Table3;
END
GO

2. New存储过程直接接收输出参数+INSERT EXEC拿第三个结果集：

CREATE OR ALTER PROCEDURE New
AS
BEGIN
    DECLARE @Xml1 XML, @Xml2 XML;
    CREATE TABLE #Result3 (DateValue DATE);

    EXEC Old @OutXml1 = @Xml1 OUTPUT, @OutXml2 = @Xml2 OUTPUT;
    INSERT INTO #Result3 EXEC Old @OutXml1 = @Xml1 OUTPUT, @OutXml2 = @Xml2 OUTPUT;

    -- 直接使用@Xml1、@Xml2和#Result3处理业务即可
END
GO

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