Retrieve data from stored procedure which has multiple result sets

Question

Given a stored procedure in SQL Server which has multiple select statements, is there a way to work with those results separately while calling the procedure?

For example:

alter procedure dbo.GetSomething
as
begin
    select * from dbo.Person;
    select * from dbo.Car;
end;

In .NET, if I call this proc, I can use a SqlDataReader to move between the two result sets, so I can easily retrieve all people and cars. In SQL however, when I execute the proc directly, I get both result sets.

If I call:

insert @myTempTable
    exec dbo.GetSomething;

Then it errors because the column definition doesn't match. If by some chance Person and Car have the same columns, it concatenates the two together, and @myTempTable gets all records from both tables, which obviously is no good either.

I can define new custom types representing the two result sets, and make those output parameters instead of having the multiple select statements, but I'm wondering if there's a better way - some way of pulling both results into temporary tables, or looping through the results, or something.

EDIT

Actually, after looking more closely, even output table parameters won't solve this - they're readonly, and that's still true in SQL 2012. (Connect ticket asking for this to be added)

Answer 1

In TSQL land, you're stuck.

Here is a trick (some may call semi-hacky) way that I used one time.

/*  START TSQL CODE */

/*  Stored Procedure Definition */

Use Northwind
GO


IF EXISTS 
    (
    SELECT * FROM INFORMATION_SCHEMA.ROUTINES
    WHERE ROUTINE_TYPE = N'PROCEDURE' and ROUTINE_SCHEMA = N'dbo' and ROUTINE_NAME = N'uspOrderDetailsByCustomerId'  
    )
BEGIN
    DROP PROCEDURE [dbo].[uspOrderDetailsByCustomerId]
END


GO

CREATE Procedure dbo.uspOrderDetailsByCustomerId
(
  @CustomerID nchar(5)
, @ResultSetIndicator smallint = 0
)
AS

BEGIN

    SET NOCOUNT ON



    /* ResultSet #1 */

    if (@ResultSetIndicator = 0 OR @ResultSetIndicator = 1)
    BEGIN 
        SELECT 
            c.CustomerID, c.CompanyName /*,c.ContactName,c.ContactTitle,c.[Address],c.City,c.Region,c.PostalCode,c.Country ,c.Phone,c.Fax */
        FROM 
            Customers c 
            JOIN Orders o ON c.CustomerID = o.CustomerID 
        WHERE 
            c.CustomerID = @CustomerID
    END


    /* */
    /* ResultSet #2 */ 

    if (@ResultSetIndicator = 0 OR @ResultSetIndicator = 2)
    BEGIN 

        SELECT o.OrderID,o.CustomerID /* ,o.EmployeeID,o.OrderDate,o.RequiredDate,o.ShippedDate,o.ShipVia ,o.Freight,o.ShipName,o.ShipAddress,o.OrderID,o.CustomerID,o.EmployeeID,o.OrderDate  */
        FROM 
            Orders o 
         WHERE 
            o.CustomerID = @CustomerID
        ORDER BY 
            o.CustomerID , o.OrderID 

    END


    /* */
    /* ResultSet #3 */

    if (@ResultSetIndicator = 0 OR @ResultSetIndicator = 3)
    BEGIN 
         SELECT od.OrderID,od.ProductID /* ,od.UnitPrice,od.Quantity,od.Discount  */
         FROM 
            [Order Details] od 
         WHERE 
            exists (select null from dbo.Orders  innerOrds where innerOrds.OrderID = od.OrderID and innerOrds.CustomerID = @CustomerID )
         ORDER BY 
            od.OrderID 

    END

    SET NOCOUNT OFF


END
 
GO 
/* Get everything */


exec dbo.uspOrderDetailsByCustomerId 'ALFKI'




    IF OBJECT_ID('tempdb..#TempCustomer') IS NOT NULL
    begin
            drop table #TempCustomer
    end


    CREATE TABLE #TempCustomer
    ( 
      [CustomerID] nchar(5)
    , [CompanyName] nvarchar(40)
    )

INSERT INTO #TempCustomer ( [CustomerID] , [CompanyName])
exec dbo.uspOrderDetailsByCustomerId 'ALFKI' , 1

Select * from #TempCustomer



    IF OBJECT_ID('tempdb..#TempOrders') IS NOT NULL
    begin
            drop table #TempOrders
    end


    CREATE TABLE #TempOrders
    ( 
        OrderID int
      , [CustomerID] nchar(5)

    )

INSERT INTO #TempOrders ( OrderID , [CustomerID] )
exec dbo.uspOrderDetailsByCustomerId 'ALFKI' , 2

Select * from #TempOrders






    IF OBJECT_ID('tempdb..#TempOrderDetails') IS NOT NULL
    begin
            drop table #TempOrderDetails
    end


    CREATE TABLE #TempOrderDetails
    ( 
        OrderID int
      , [ProductID] int

    )

INSERT INTO #TempOrderDetails ( OrderID , [ProductID] )
exec dbo.uspOrderDetailsByCustomerId 'ALFKI' , 3

Select * from #TempOrderDetails


    IF OBJECT_ID('tempdb..#TempOrderDetails') IS NOT NULL
    begin
            drop table #TempOrderDetails
    end


    IF OBJECT_ID('tempdb..#TempOrders') IS NOT NULL
    begin
            drop table #TempOrders
    end



    IF OBJECT_ID('tempdb..#TempCustomer') IS NOT NULL
    begin
            drop table #TempCustomer
    end

Answer 2

To retrieve multiple result sets using a DataReader with NextResult you can do this

String myConStr  
          ="User ID="user";password="pass";Initial Catalog=pubs;Data Source=Server";
SqlConnection myConnection = new SqlConnection(myConStr);
SqlCommand myCommand = new SqlCommand();
SqlDataReader myReader;

myCommand.CommandType = CommandType.StoredProcedure;
myCommand.Connection = myConnection;
myCommand.CommandText = "MyProc";
     
try
{
   myConnection.Open();
   myReader = myCommand.ExecuteReader();

   while (myReader.Read())
   {
      // write logic to process data for the first result.   
   }
    
   myReader.NextResult();
   while (myReader.Read())
   {
      // write logic to process data for the second result.
   }
}

Answer 3

Would passing a parameter to the sp do the trick?

CREATE PROCEDURE  dostuff @parm1 int
AS

BEGIN
Declare @resultset Int
Set @resultset = @parm1

--0 = Select ranks
--1 = Select suits
--other - Select ALL

If @resultset = 0 
 SELECT [rank] FROM [ranks]
 Else If @resultset = 1
 SELECT [suit] FROM [suits]
 Else 
 SELECT * FROM [suits]
 cross join   [ranks] 
END
GO

 declare @mytemptbl table (rank text)
 insert @mytemptbl
  exec dostuff 0

 select * from @mytemptbl

Answer 4

A usable approach is to invert the process and have the stored procedure accept names of temporary tables and use dynamic SQL to fill in the temporary tables with the desired results.

The caller has access to the data contained in the temporary tables when the SP returns.

-- local temporary tables avoid leaking to other sessions
create table #fast_cars (name varchar(max), top_speed float);
create table #awesome_people (name varchar(max), age int);

-- invoked SPs can access local temporary tables in scope
exec GetAmazingThings @name='%Wonder%'
    ,@cars='#fast_cars'
    ,@people='#awesome_people'

-- use 'returned' data
select name, top_speed from #fast_cars;
select name, age from #awesome_people;

Accepting the table names reduces “magic knowledge” of what tables are affected as the names are explicitly supplied. It also allows collecting the results of multiple calls while maintaining isolation, including during nesting.

The stored procedure might look a bit like..

create procedure GetAmazingThings
    @name varchar(100),
    -- output table names..
    ,@cars varchar(100)
    ,@people varchar(100)
as
    set @cars = quotename(@cars); -- bobby is not welcome
    declare @sql nvarchar(max);

    declare #_cars (name varchar(max), top_speed float);

    -- perform queries into local temp tables
    -- (this could also be done as the dynamic SQL to avoid a copy)
    insert into #_cars (name, top_speed)
    select Name, max(LapSpeed)
    from TonkaToys
    where Name like @name and VehicleType = 'car'
    group by Name;

    if patindex('[[]#%', @cars) > 0 -- given temp table
    begin
        -- copy result to supplied temp table
        set @sql = concat(N'
insert into ', @cars, ' (name, top_speed)
select name, top_speed
from #_cars
');
        exec sp_executesql @sql;
    end
    else
    begin
        -- just select result
        select name, top_speed from #cars
    end

    -- ditto for @people query/results
go

Notes:

• There is at least one (and possibly more) data copies between temporary tables.
• It is a bit cleaner in the SP if the dynamic SQL is isolated from the primary query. First query into a local temporary table and then copy that into the supplied temporary table using dynamic SQL.
• If using global temporary tables, the SP can create the required table result-set. However, while handy, this can be problematic because the global temporary tables are shared between sessions.
• The parameters can also be used to control what the SP “returns”, eg. skip the query, or select as result-set instead of writing to the temp table.

Answer 5

I know I am a little late to the party on this one but adding this only to help anyone else that comes across this thread and needs another option.

I recommend that you split the procedure calls up as indicated in the accepted answer by Joe Enos if you have that option, but if you do not then the information in this link may be an option for you.

https://khalidabuhakmeh.com/entity-framework-6-multiple-result-sets-with-stored-procedures

This is the approach I took for working with a procedure call that could not be split up that returned 6 different query results. The approach that I took was based largely on the information in that article and made this very easy and testable.

Answer 6

Create an SqlDataAdapter, set its SelectCommand to execute the SP "GetSomething", and then use the data adapter to fill a DataSet. The DataSet will contain as many DataTable's as you have "select" statements returning recordsets from the SP.

Here's what your code would look like:

System.Data.SqlClient.SqlDataAdapter da = new System.Data.SqlClient.SqlDataAdapter();
System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand();
cmd.Connection = myConnectionObject;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "GetSomething";
da.SelectCommand = cmd;

System.Data.DataSet ds = new DataSet();
da.Fill(ds);
// at this point, the (ds) object contains DataTables created from the recordsets returned by the SP
DataTable dt0 = ds.Tables[0];
DataTable dt1 = ds.Tables[1];

// note that dt0 corresponds to the FIRST recordset returned by the SP, etc.

Answer 7

You can put multiple result set in form of xml to a table

So that when you want to access all these result you parse those result set column to a tabular form

Answer 8

It seems like there's no good simple way to do this, without a hack or a major paradigm shift. It looks like the best way is to just split out the original procs and end up with one more proc than before:

Old way:

create procedure dbo.GetSomething
as
begin
    select * from dbo.Person;
    select * from dbo.Car;
end;

New way:

create procedure dbo.GetPeople
as
begin
    select * from dbo.Person;
end;

create procedure dbo.GetCars
as
begin
    select * from dbo.Car;
end;

-- This gives the same result as before
create procedure dbo.GetSomething
as
begin
    exec dbo.GetPeople;
    exec dbo.GetCars;
end;

Then when I'm in a different proc and need both result sets, I'd just have to call them one at a time.

Answer 9

While this does not appear to be supported natively in T-SQL, if using a CLR Stored Procedure is an option for you, then you should be able to create a Stored Procedure in your preferred .Net language that uses the SqlDataReader.NextResult() method to advance to the desired result set and then send the SqlDataReader back via the SqlPipe.Send(SqlDataReader) method. You would just need to pass in the SQL to execute and the desired result set as parameters to this proc.

This would allow you to work with the proc as is, without modifying it to send back all or only one result set.

Answer 10

Der. Read the whole question before writing an answer! :-P

If you're trying to work with the results in TSQL land you're going to need to use some way to keep the results separate. Writing results to Temp tables is possibly your best bet since you won't need to depend on columns lining up (or not, as the case may be) and can deal with the data in a "natural" fashion for SQL Server. E.g.

create proc test_something
as begin
    select a, b into temp1 from table1
    select b, c into temp2 from table2
end
go

exec dbo.test_something()

select * from temp1
select * from temp2