Separate Stored Procedure result sets into Temporary tables

3
votes

I have a a stored procedure that returns 2 result sets. I'm trying to call the stored procedure and insert the values of the first result set into a temporary table, but I am unable to do this because I'm returning 2 result sets.

Is there a way to return just one result set or both of them into seperate temporary tables. I am unable to change the stored procedure.

Stored procedure result set 1

column a | column b | coulmn c

Stored procedure result set 2

column x | column y

What I am doing is 

DECLARE @ResultSet1Table 
TABLE (
        column a
       ,column b
       ,column c
       )

INSERT INTO @ResultSet1Table 
   EXEC StoredProc

And receiveing the error message

Column name or number of supplied values does not match table definition

because of the second result set.

2
sql-servertsqlstored-procedures
I've looked into this before and I don't think there's a way to do it. I'm hoping someone provides an answer that makes me look like an idiot though. - TZHX
which version of sql server are you using? - flo
Why don't to declare 2 table variables and then merge them? - Giorgi Nakeuri
I have a decent workaround assuming you can slightly modify the stored procedure - Stephan

2 Answers

2
votes

Ok this is a bit of a hack:

CREATE PROCEDURE SPMultipleResultsSets
AS

SELECT *
FROM 
    ( VALUES (1),(2),(3),(4)) Vals(Num)

SELECT *
FROM 
    ( VALUES ('ABC'),('DEF'),('GHI'),('JKL')) Strings(string)

You need to have ad-hoc distributed queries turned on:

sp_configure 'Show Advanced Options', 1
GO
RECONFIGURE
GO
sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE
GO

SELECT * INTO #Temp FROM OPENROWSET('SQLNCLI', 'Server=localhost;Trusted_Connection=yes;',
'EXEC DBNAME.dbo.SPMultipleResultsSets')
-- Select Table
SELECT *
FROM #Temp;

Returns:

Num
1
2
3
4
0
votes

I think I have a reasonable work around. Just add columns to identify each result set. Then separate them with queries. Check it out:

CREATE PROCEDURE usp_test
AS
SELECT  colA = 'A',
        colB = 'B',
        colC = 'C';

SELECT  colX = 'X',
        colY = 'Y',
        '!';
GO

DECLARE @ResultSetTable TABLE(col1 CHAR,col2 CHAR,col3 CHAR);

INSERT INTO @ResultSetTable
    EXEC usp_test

--Set 1
SELECT *
FROM @ResultSetTable
WHERE col3 <> '!'

--Set 2
SELECT *
FROM @ResultSetTable
WHERE col3 ='!'

Results for Set 1:

col1 col2 col3
---- ---- ----
A    B    C

Results for Set 2:

col1 col2 col3
---- ---- ----
X    Y    !