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)
See Question&Answers more detail:
os 与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…