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

String myConnString  = "User ID="username";password="password";Initial Catalog=pubs;Data Source=Server"; SqlConnection myConnection = new SqlConnection(myConnString); 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.     } }