SQL Server 2012：如何将存储过程的两个结果集分别插入临时表

嘿，这个问题我之前在项目里也踩过坑！SQL Server 默认用 INSERT INTO #Temp EXEC [dbo].[SomeSP] 确实只会抓第一个结果集，剩下的直接忽略。下面给你两个靠谱的解决方案，你根据自己的场景选：

方案一：修改原存储过程（最简单，若有权限修改） #

如果你能改动原存储过程，这绝对是最省心的办法。我们可以让存储过程把两个结果集写入全局临时表，调用方直接读取就行：

步骤1：修改存储过程 #

ALTER PROCEDURE [dbo].[SomeSP]
AS
BEGIN
    SET NOCOUNT ON;

    -- 清理可能存在的全局临时表（避免冲突）
    IF OBJECT_ID('tempdb..##SPTemp1') IS NOT NULL 
        DROP TABLE ##SPTemp1;
    IF OBJECT_ID('tempdb..##SPTemp2') IS NOT NULL 
        DROP TABLE ##SPTemp2;

    -- 第一个结果集写入全局临时表
    SELECT * INTO ##SPTemp1
    FROM (
        -- 替换成原存储过程生成第一个结果集的查询逻辑
        SELECT Col1, Col2 FROM YourSourceTable1
    ) AS Temp;

    -- 第二个结果集写入全局临时表
    SELECT * INTO ##SPTemp2
    FROM (
        -- 替换成原存储过程生成第二个结果集的查询逻辑
        SELECT ID, Description FROM YourSourceTable2
    ) AS Temp;
END

步骤2：调用存储过程并获取结果 #

-- 执行存储过程，生成全局临时表
EXEC [dbo].[SomeSP];

-- 将全局临时表的数据导入到自己的本地临时表（可选，也可以直接使用全局表）
SELECT * INTO #Temp1 FROM ##SPTemp1;
SELECT * INTO #Temp2 FROM ##SPTemp2;

-- 验证结果
SELECT * FROM #Temp1;
SELECT * FROM #Temp2;

-- 清理全局临时表（可选，会话结束后会自动删除）
DROP TABLE ##SPTemp1;
DROP TABLE ##SPTemp2;

方案二：使用CLR存储过程（不修改原SP，仅执行一次） #

如果原存储过程不能改，而且有写操作、不能重复执行，那CLR是最稳妥的方案。它能在一次执行中读取所有结果集，然后插入到对应的临时表中：

步骤1：编写CLR代码 #

创建一个C#类库，写一个方法来读取存储过程的所有结果集：

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

public class SPResultCapture
{
    [SqlProcedure]
    public static void CaptureMultipleResults(string spName, string tempTable1, string tempTable2)
    {
        // 使用上下文连接，无需额外配置数据库连接字符串
        using (var conn = new SqlConnection("Context Connection=true"))
        {
            conn.Open();
            var cmd = new SqlCommand(spName, conn) { CommandType = CommandType.StoredProcedure };

            using (var reader = cmd.ExecuteReader())
            {
                // 读取第一个结果集，批量插入到第一个临时表
                using (var bulkCopy = new SqlBulkCopy(conn))
                {
                    bulkCopy.DestinationTableName = tempTable1;
                    bulkCopy.WriteToServer(reader);
                }

                // 切换到第二个结果集，批量插入到第二个临时表
                if (reader.NextResult())
                {
                    using (var bulkCopy = new SqlBulkCopy(conn))
                    {
                        bulkCopy.DestinationTableName = tempTable2;
                        bulkCopy.WriteToServer(reader);
                    }
                }
            }
        }
    }
}

步骤2：部署CLR到SQL Server #

1. 把上面的代码编译成DLL文件。
2. 在SQL Server中启用CLR集成：

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

3. 创建程序集和CLR存储过程：

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

CREATE PROCEDURE dbo.CaptureMultipleResults
    @SPName NVARCHAR(128),
    @TempTable1 NVARCHAR(128),
    @TempTable2 NVARCHAR(128)
AS EXTERNAL NAME SPResultCaptureAssembly.SPResultCapture.CaptureMultipleResults;

步骤3：使用CLR存储过程捕获结果 #

-- 创建两个临时表，结构必须和SP的两个结果集完全匹配
CREATE TABLE #Temp1 (
    Col1 INT,
    Col2 VARCHAR(50)
    -- 其他列按实际结果集定义
);

CREATE TABLE #Temp2 (
    ID INT,
    Description TEXT
    -- 其他列按实际结果集定义
);

-- 调用CLR存储过程，一次性捕获两个结果集
EXEC dbo.CaptureMultipleResults '[dbo].[SomeSP]', '#Temp1', '#Temp2';

-- 查看结果
SELECT * FROM #Temp1;
SELECT * FROM #Temp2;

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