One of my friends asked me if it is possible to load multiple resultsets returned from a stored procedure into a temporary table for some analysis purpose. The answer is “It depends”. If all the result sets return same number of columns then it is possible otherwise it is not possible.
Create the following stored procedure
USE tempdb GO CREATE PROCEDURE proc_test AS SELECT 1003 AS number SELECT 28 AS number SELECT 987345 AS number
When you execute this procedure
You get three different result sets
number ----------- 1003 number ----------- 28 number ----------- 987345
Now create a temporary table
CREATE TABLE #temp ( number INT )
Add data to this table by executing the stored procedure
INSERT INTO #temp ( number ) EXEC proc_test
Now check the result from this temporary table
SELECT number FROM #temp
The result is
number --------- 1003 28 987345
So if the table structure is similar to the result set of the stored procedure, it does not matter how many similar result sets are returned, it can be imported to the table. However Currently it is not possible to identify a specific result set from the stored procedure.