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)
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.