How to import multiple result sets from Stored procedure?

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

  EXEC proc_test

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.

Advertisements

2 thoughts on “How to import multiple result sets from Stored procedure?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s