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 pu…

Accepted Answer

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.

Retrieve data from stored procedure which has multiple result sets

Solution

Problem