SQL Server Procedure returns multiple tables - Insert results into tables

Question

I have a procedure that returns multiple tables; eg:

PROCEDURE Something AS
BEGIN
 SELECT 1,2,3
 SELECT 4,5
 SELECT 9,10,11
END

I would like to take each table from the result and insert it into a series of tables/temp tables - one for each record set.

Is this possible?

Answer 1

if you Union the results together they would come out as one result set.

your second query only has 2 columns but this would need to be resolved either way as you put it into a table.

Answer 2

You could create the temporary tables within the stored proc and push the records into that. If you are using the same session , the table would be available after the stored proc is finished.

Or you could create the temp tables before hand and call the sp to populate them.

Answer 3

Check out Multiple Active Resultsets (MARS). It may do what you are looking for.

http://www.sqlteam.com/article/multiple-active-result-sets-mars

http://blogs.msdn.com/sqlprogrammability/archive/2006/05/01/MARSIntroduction1.aspx