如何在存储过程中获取其他存储过程的结果集以适配SSRS？

方案一：修改原存储过程（推荐，最简单） #

如果允许修改原存储过程，可以添加参数控制返回的结果集，直接返回合并后的单一结果集，避免多余输出：

CREATE PROCEDURE Proc_Original
    @ReturnCombined BIT = 0 -- 新增参数，控制是否返回合并结果集
AS
BEGIN
    SET NOCOUNT ON;

    -- 原有5个结果集的逻辑（保留，不影响原有调用）
    SELECT ID, Name FROM Table1;
    SELECT OrderID, Amount FROM Table2;
    SELECT ProductID, Category FROM Table3;
    SELECT CustomerID, Country FROM Table4;
    SELECT ReportDate, Total FROM Table5;

    -- 如果需要返回合并结果集，执行以下逻辑
    IF @ReturnCombined = 1
    BEGIN
        -- 合并第1、3、5个结果集，添加标识列区分类型
        SELECT 'Result1' AS ResultType, ID, Name, NULL AS ProductID, NULL AS Category, NULL AS ReportDate, NULL AS Total
        FROM Table1
        -- 复制原存储过程中第一个SELECT的WHERE/ JOIN等逻辑

        UNION ALL

        SELECT 'Result3' AS ResultType, NULL AS ID, NULL AS Name, ProductID, Category, NULL AS ReportDate, NULL AS Total
        FROM Table3
        -- 复制原存储过程中第三个SELECT的逻辑

        UNION ALL

        SELECT 'Result5' AS ResultType, NULL AS ID, NULL AS Name, NULL AS ProductID, NULL AS Category, ReportDate, Total
        FROM Table5
        -- 复制原存储过程中第五个SELECT的逻辑
    END
END

在SSRS中调用时，执行EXEC Proc_Original @ReturnCombined = 1，即可获取单一合并结果集，SSRS可以正常读取。

方案二：复制需要的逻辑到新存储过程（无需修改原存储过程） #

如果不能修改原存储过程，直接复制原存储过程中需要的3个SELECT语句到新存储过程，合并后返回：

CREATE PROCEDURE Proc_CombinedForSSRS
AS
BEGIN
    SET NOCOUNT ON;

    -- 复制原存储过程第1个结果集的逻辑
    SELECT 'Result1' AS ResultType, ID, Name, NULL AS ProductID, NULL AS Category, NULL AS ReportDate, NULL AS Total
    FROM Table1
    -- 保留原存储过程中该SELECT的所有过滤、关联逻辑

    UNION ALL

    -- 复制原存储过程第3个结果集的逻辑
    SELECT 'Result3' AS ResultType, NULL AS ID, NULL AS Name, ProductID, Category, NULL AS ReportDate, NULL AS Total
    FROM Table3
    -- 保留原存储过程中该SELECT的所有逻辑

    UNION ALL

    -- 复制原存储过程第5个结果集的逻辑
    SELECT 'Result5' AS ResultType, NULL AS ID, NULL AS Name, NULL AS ProductID, NULL AS Category, ReportDate, Total
    FROM Table5
    -- 保留原存储过程中该SELECT的所有逻辑
END

这个方案的优点是完全隔离，不会影响原存储过程的使用，且没有多余结果集输出，SSRS直接调用Proc_CombinedForSSRS即可。

方案三：使用CLR存储过程捕获所有结果集（适合无法修改原存储过程且逻辑复杂的场景） #

如果原存储过程逻辑复杂、频繁变动，复制逻辑维护成本高，可以编写CLR存储过程捕获原存储过程的所有结果集，筛选后合并返回：

步骤1：编写CLR代码 #

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public class StoredProcedures
{
    [SqlProcedure]
    public static void GetCombinedSSRSResults()
    {
        using (SqlConnection conn = new SqlConnection("Context Connection=true"))
        {
            conn.Open();
            SqlCommand cmd = new SqlCommand("Proc_Original", conn);
            cmd.CommandType = CommandType.StoredProcedure;

            SqlDataReader reader = cmd.ExecuteReader();
            DataTable combinedTable = new DataTable();

            // 定义合并结果集的结构
            combinedTable.Columns.Add("ResultType", typeof(string));
            combinedTable.Columns.Add("ID", typeof(int)).AllowDBNull = true;
            combinedTable.Columns.Add("Name", typeof(string)).AllowDBNull = true;
            combinedTable.Columns.Add("ProductID", typeof(int)).AllowDBNull = true;
            combinedTable.Columns.Add("Category", typeof(string)).AllowDBNull = true;
            combinedTable.Columns.Add("ReportDate", typeof(DateTime)).AllowDBNull = true;
            combinedTable.Columns.Add("Total", typeof(int)).AllowDBNull = true;

            int resultSetIdx = 0;
            do
            {
                resultSetIdx++;
                // 仅处理第1、3、5个结果集
                if (resultSetIdx is 1 or 3 or 5)
                {
                    string typeLabel = $"Result{resultSetIdx}";
                    while (reader.Read())
                    {
                        DataRow row = combinedTable.NewRow();
                        row["ResultType"] = typeLabel;
                        switch (resultSetIdx)
                        {
                            case 1:
                                row["ID"] = reader["ID"];
                                row["Name"] = reader["Name"];
                                break;
                            case 3:
                                row["ProductID"] = reader["ProductID"];
                                row["Category"] = reader["Category"];
                                break;
                            case 5:
                                row["ReportDate"] = reader["ReportDate"];
                                row["Total"] = reader["Total"];
                                break;
                        }
                        combinedTable.Rows.Add(row);
                    }
                }
            } while (reader.NextResult());

            // 返回合并后的结果集
            SqlContext.Pipe.Send(combinedTable);
        }
    }
}

步骤2：部署CLR存储过程 #

1. 将代码编译为DLL文件。
2. 在SQL Server中启用CLR：

sp_configure 'show advanced options', 1;
RECONFIGURE;
sp_configure 'clr enabled', 1;
RECONFIGURE;

3. 注册程序集并创建存储过程：

CREATE ASSEMBLY SSRSResultCombiner
FROM 'C:\Path\To\Your\DLL\File.dll'
WITH PERMISSION_SET = SAFE;

CREATE PROCEDURE Proc_CombinedCLR
AS EXTERNAL NAME SSRSResultCombiner.StoredProcedures.GetCombinedSSRSResults;

之后在SSRS中调用Proc_CombinedCLR即可获取单一结果集，且不会输出原存储过程的多余结果。

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