INSERT INTO with exec with multiple result sets

问题:

SQL Server allows me to insert the returned result set of a stored procedure as:

DECLARE @T TABLE (
  ID int,
  Name varchar(255),
  Amount money)

INSERT INTO @T
exec dbo.pVendorBalance 

This works as long as the stored procedure only returns 1 result set.

Is there a way to make this work if the stored procedure returns several result sets?

E.g.

DECLARE @T1 (...)
DECLARE @T2 (...)

INSERT INTO @T1 THEN INTO @T2
exec dbo.pVendorBalance 

回答1:

I had a similar requirement, and ended up using the a CLR function which you can read about here (it's the answer with the InsertResultSetsToTables method, by user Dan Guzman):

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

You need to create a SQL Server CLR project in Visual Studio to get going. I had a project already written by a co-worker that I could just expand, but if you're starting from scratch, try reading this guide:

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

If you've succeeded in writing and publishing the CLR project to the database, here is an example of using it I wrote:

-- 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;

回答2:

No. But there is more of a work around since you cannot do an insert into with a procedure that returns multiple results with a different number of columns.

If you are allowed to modify the stored procedure, then you can declare temp tables outside of the procedure and populate them within the stored procedure. Then you can do whatever you need with them outside of the stored procedure.

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;

回答3:

One workaround to this problem is using OUTPUT parameters (JSON/XML) instead of resultsets.

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;

Version with OUT params:

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;

And final call:

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 Demo

EDIT:

Second approach is to use tSQLt.ResultSetFilter CLR function (part of tSQLt testing framework):

The ResultSetFilter procedure provides the ability to retrieve a single result set from a statement which produces multiple result sets.

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;

回答4:

Actually stored procedures can return multiple result sets, or no result sets, it's pretty arbitrary. Because of this, I don't know of any way to navigate those results from other SQL code calling a stored procedure.

However, you CAN use the returned result set from a table-valued user defined function. It's just like a regular UDF, but instead of returning a scalar value you return a query result. Then you can use that UDF like any other table.

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

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

回答5:

If the both result sets have same number of columns then

insert into @T1 exec dbo.pVendorBalance

will insert the union of both data set into @T1.

If not

Then edit dbo.pVendorBalance and insert results into temporary tables and in outer stored proc, select from those temporary tables.

Another way(If you need it), you can try

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

it will take first dataset.