Receive multiple result sets in stored procedure

Question

I have a procedure Sp1:

Begin
    select product_id, product_name 
    from product

    select dept_id, dept_name 
    from department
end

My procedure returns two result sets, now I call to this procedure in another procedure using:

exec SP1

How can I access the results of SP1 in this other procedure?

_"How i **stored result of this SP1 in another procedure**"_ What that mean? Can't you use a #TempTable? or even better a Function? — Ilyes, Nov 24 '18 at 11:16
As far as I know, you can only capture the last "result set" from the stored procedure. As far as I'm concerned, that's a total hack anyway, and you should look into user defined functions or output variables to return values. — Gordon Linoff, Nov 24 '18 at 12:04
Possible duplicate of [Retrieve data from stored procedure which has multiple result sets](https://stackoverflow.com/questions/20082889/retrieve-data-from-stored-procedure-which-has-multiple-result-sets) — Kevin, Nov 24 '18 at 12:43
This is an [XY Problem](http://xyproblem.info/). What are you actually trying to accomplish with the results of those queries? — Eric Brandt, Nov 25 '18 at 09:46

Thom A · Answer 1 · 2018-11-24T13:06:36.383

You can get the results from an SP into a table by using the INSERT INTO..EXEC syntax. I don't advise it, however, as it relies on all datasets being returned from the SP to have the same definition:

USE Sandbox;
GO

CREATE PROC TestProc1 AS

    SELECT *
    FROM (VALUES(1,'T-Shirt'),
                (2,'Jeans'),
                (3,'Spotlight')) V(ProductID,ProductName);

    SELECT *
    FROM (VALUES(1,'Clothing'),
                (2,'Lighting')) V(DeptID, DepartmentName);
GO

CREATE TABLE #TempTable (ID int, [Name] varchar(15));

INSERT INTO #TempTable
EXEC TestProc1;

SELECT *
FROM #TempTable;

GO

DROP TABLE #TempTable
DROP PROC TestProc1;

As soon as you throw in a dataset that has a different definition (for example, different number of columns, or perhaps a value that can't be implicitly cast (i.e. 'abc' to an int) it'll fail. For example:

USE Sandbox;
GO

CREATE PROC TestProc1 AS

    SELECT *
    FROM (VALUES(1,'T-Shirt',1),
                (2,'Jeans',1),
                (3,'Spotlight',2)) V(ProductID,ProductName,DeptID);

    SELECT *
    FROM (VALUES(1,'Clothing'),
                (2,'Lighting')) V(DeptID, DepartmentName);
GO

CREATE TABLE #TempTable (ID int, [Name] varchar(15));

--fails
INSERT INTO #TempTable
EXEC TestProc1;

SELECT *
FROM #TempTable;

GO

DROP TABLE #TempTable;
GO

CREATE TABLE #TempTable (ID int, [Name] varchar(15),OtherID int);

--fails   
INSERT INTO #TempTable
EXEC TestProc1;

SELECT *
FROM #TempTable;

GO

DROP TABLE #TempTable
DROP PROC TestProc1;

You should really be using multiple SP's and handling the data that way.

The first comment in the first script says fails but I think that should be succeeds. — Dan Guzman, Nov 24 '18 at 13:04
I want this multiple result set in another stored procedure not in controller. This two result set come after lot of calculation. — , Nov 27 '18 at 05:27
You're statement be very vague. You can't pass datasets between stored proecures. You could (again) use a temporary table though. — Thom A, Nov 27 '18 at 08:59

Receive multiple result sets in stored procedure

1 Answers1