使用具有多个结果集的可执行文件插入 INTO

Question

SQL Server 允许我将存储过程的返回结果集插入为：

DECLARE @T TABLE (
  ID int,
  Name varchar(255),
  Amount money)
INSERT INTO @T
exec dbo.pVendorBalance 

只要存储过程仅返回 1 个结果集，此操作就有效。

如果存储过程返回多个结果集，有没有办法使它工作？

例如

DECLARE @T1 (...)
DECLARE @T2 (...)
INSERT INTO @T1 THEN INTO @T2
exec dbo.pVendorBalance 

Answer 1

此问题

的一种解决方法是使用OUTPUT参数 （JSON/XML） 而不是结果集。

CREATE TABLE tab1(ID INT, Name NVARCHAR(10), Amount MONEY);
INSERT INTO tab1(ID, Name, Amount)
VALUES (1, 'Alexander', 10),(2, 'Jimmy', 100), (6, 'Billy', 20);
CREATE PROCEDURE dbo.pVendorBalance 
AS
BEGIN
   -- first resultset
   SELECT * FROM tab1 WHERE ID <=2;
   -- second resultset
   SELECT * FROM tab1 WHERE ID > 5;
END;

带 OUT 参数的版本：

CREATE PROCEDURE dbo.pVendorBalance2 
         @resultSet1 NVARCHAR(MAX) OUT,
         @resultSet2 NVARCHAR(MAX) OUT
AS
BEGIN
    SELECT @resultSet1 = (SELECT * FROM tab1 WHERE ID <=2 FOR JSON AUTO),
           @resultSet2 = (SELECT * FROM tab1 WHERE ID > 5 FOR JSON AUTO);
END;

最后的电话：

DECLARE @r1 NVARCHAR(MAX), @r2 NVARCHAR(MAX);
EXEC dbo.pVendorBalance2 @r1 OUT, @r2 OUT;

-- first resultset as table
SELECT * 
INTO #t1
FROM OpenJson(@r1)
WITH (ID int '$.ID', [Name] NVARCHAR(50) '$.Name',Amount money '$.Amount');
-- second resultset as table
SELECT *  
INTO #t2
FROM OpenJson(@r2)
WITH (ID int '$.ID', [Name] NVARCHAR(50) '$.Name',Amount money '$.Amount');
SELECT * FROM #t1;
SELECT * FROM #t2;

DBFiddle 演示

编辑：

第二种方法是使用 tSQLt.ResultSetFilter CLR 函数（tSQLt 测试框架的一部分）：

ResultSetFilter 过程提供了从生成多个结果集的语句中检索单个结果集的功能。

CREATE TABLE #DatabaseSize (
    database_name nvarchar(128),
    database_size varchar(18),
    unallocated_space varchar(18)
);
CREATE TABLE #ReservedSpaceUsed (
    reserved VARCHAR(18),
    data VARCHAR(18),
    index_size VARCHAR(18),
    unused VARCHAR(18)
);
INSERT INTO #DatabaseSize
EXEC tSQLt.ResultSetFilter 1, 'EXEC sp_spaceused';
INSERT INTO #ReservedSpaceUsed
EXEC tSQLt.ResultSetFilter 2, 'EXEC sp_spaceused';
SELECT * FROM #DatabaseSize;
SELECT * FROM #ReservedSpaceUsed;

Answer 2

No. 但是还有更多的解决方法，因为您无法使用返回具有不同列数的多个结果的过程进行插入。

如果允许修改存储过程，则可以在过程外部声明临时表，并在存储过程中填充它们。 然后，您可以在存储过程之外对它们执行任何需要的操作。

CREATE TABLE #result1(Each column followed by data type of first result.);
----Example:  CREATE TABLE #result1(Column1 int, Column2 varchar(10))
CREATE TABLE #result2(Each column followed by data type of second result.);
EXEC pVendorBalance;
SELECT * FROM #result1;
SELECT * FROM #result2;

Answer 3

我也有类似的要求，最终使用了 CLR 函数，您可以在此处阅读（这是用户 Dan Guzman 使用 InsertResultSetsToTables 方法的答案）：

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/da5328a7-5dab-44b3-b2b1-4a8d6d7798b2/insert-into-table-one-or-multiple-result-sets-from-stored-procedure?forum=transactsql

您需要在Visual Studio中创建一个SQL Server CLR项目才能开始。我有一个由同事编写的项目，我可以扩展，但如果您是从头开始，请尝试阅读本指南：

http://www.emoreau.com/Entries/Articles/2015/04/SQL-CLR-Integration-in-2015-year-not-product-version.aspx

如果您已成功编写 CLR 项目并将其发布到数据库，下面是我编写的使用它的示例：

-- declare a string with the SQL you want to execute (typically an SP call that returns multiple result sets)
DECLARE @sql NVARCHAR(MAX)
SET @sql = 'exec usp_SomeProcedure @variable1 = ' + @variable1 + '...' -- piece together a long SQL string from various parameters
-- create temp tables (one per result set) to hold the output; could also be actual tables (non-temp) if you want
CREATE TABLE #results_1(
    [CustomerId] INT, [Name] varchar(500), [Address] varchar(500)
);
CREATE TABLE #results_2(
    [SomeId] UNIQUEIDENTIFIER, [SomeData] INT, [SomethingElse] DateTime
);
-- on the exemplary 'CustomerDatabase' database, there is an SP (created automatically by the SQL CLR project deployment process in Visual Studio) which performs the actual call to the .NET assembly, and executes the .NET code
-- the CLR stored procedure CLR_InsertResultSetsToTables executes the SQL defined in the parameter @sourceQuery, and outputs multiple result sets into the specified list of tables (@targetTableList)
EXEC CustomerDatabase.dbo.CLR_InsertResultSetsToTables @sourceQuery = @sql, @targetTableList = N'#results_1,#results_2';
-- The output of the SP called in @sql is now dumped in the two temp tables and can be used for whatever in regular SQL
SELECT * FROM #results_1;
SELECT * FROM #results_2;

Answer 4

我们可以通过以下方式做到这一点

将输入 SP（返回 2 个表作为输出）视为usp_SourceData

更改usp_SourceData以接受参数为 1 和 2

调整 SP 的方式如下：

usp_SourceData执行"1"，它将返回第一个表

以及何时

usp_SourceData执行"2"，它将返回第二个表。

Answer 5

实际上存储过程可以返回多个结果集，或者没有结果集，这是非常随意的。因此，我不知道有什么方法可以从调用存储过程的其他 SQL 代码中导航这些结果。

但是，您可以使用从表值用户定义函数返回的结果集。 它就像常规 UDF 一样，但不是返回标量值，而是返回查询结果。 然后，您可以像使用任何其他表一样使用该 UDF。

INSERT INTO @T SELECT * FROM dbp.pVendorBalanceUDF()

http://technet.microsoft.com/en-us/library/ms191165（v=sql.105）.aspx

Answer 6

DROP TABLE ##Temp
DECLARE @dtmFrom VARCHAR(60) = '2020-12-01 00:00:00', @dtmTo VARCHAR(60) = '2020-12-02 23:59:59.997',@numAdmDscTransID VARCHAR(60) =247054

declare @procname nvarchar(255) = 'spGetCashUnpaidBills', 
        @procWithParam nvarchar(255) = '[dbo].[spGetCashUnpaidBills]  @dtmFromDate= ''' +@dtmFrom+ ''' ,@dtmToDate= ''' +@dtmTo+''',@numCompanyID=1,@numAdmDscTransID='+ @numAdmDscTransID +',@tnyShowIPCashSchemeBills=1',
        @sql nvarchar(max),
        @tableName Varchar(60) = 'Temp'
set @sql = 'create table ##' + @tableName + ' ('
begin
        select      @sql = @sql + '[' + r.name + '] ' +  r.system_type_name + ','
        from        sys.procedures AS p
        cross apply sys.dm_exec_describe_first_result_set_for_object(p.object_id, 0) AS r
        where       p.name = @procname 
        set @sql = substring(@sql,1,len(@sql)-1) + ')'
        execute (@sql)
        execute('insert ##' + @tableName + ' exec ' + @procWithParam)
end
SELECT *FROM ##Temp

Answer 7

如果两个结果集具有相同的列数，则

insert into @T1 exec dbo.pVendorBalance

将两个数据集的并集插入到@T1中。

如果不是

然后编辑 dbo.pVendorBalance 并将结果插入临时表和外部存储过程，从这些临时表中进行选择。

另一种方式（如果需要），可以尝试

SELECT * into #temp 
from OPENROWSET('SQLNCLI', 'Server=(local)\(instance);Trusted_Connection=yes;',
'EXEC dbo.pVendorBalance')

它将需要第一个数据集。