Store SP output in Tables which returns >1 DataSet

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Store SP output in Tables which returns >1 DataSet

Author

Topic

bsethi24
Starting Member

25 Posts

Posted - 2012-06-22 : 10:25:10

Dear All,

Hi I have a Store Procedure which gives OUTPUT in 2 Data Sets (Tables). Now I need to store both data sets output in a table or in 2 different tables.

How can I achieve this in MS-SQL Server ? Please guide.

Example: -

EXEC Pr_Class_School_Dtl @Class = 1

Then Output is coming in 2 Data Sets (Tables): -

Data Set (Table) 1: -
Label Q1 Q2 Q3 Q4 Q5
Class I A B C D E

Data Set (Table) 2: -
Schools Board
Outlook CBSE
MS RGPV
Mac Test

If we have only one Data Set(Table) then we can do this as below. But, how can we output of Store Procedures which returns multiple data sets (tables).

Insert Into #tt (Label, Q1, Q2, Q3, Q4, Q5) EXEC Pr_Class_Question_Dtl @Class = 1

But, how can we do this for Multiple Data Sets(Tables) in MS-SQL Server ? Please guide.

Thanks & Regards,

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-06-22 : 10:42:10

quote:
Originally posted by bsethi24

Dear All,

Hi I have a Store Procedure which gives OUTPUT in 2 Data Sets (Tables). Now I need to store both data sets output in a table or in 2 different tables.

How can I achieve this in MS-SQL Server ? Please guide.

Example: -

EXEC Pr_Class_School_Dtl @Class = 1

Then Output is coming in 2 Data Sets (Tables): -

Data Set (Table) 1: -
Label Q1 Q2 Q3 Q4 Q5
Class I A B C D E

Data Set (Table) 2: -
Schools Board
Outlook CBSE
MS RGPV
Mac Test

If we have only one Data Set(Table) then we can do this as below. But, how can we output of Store Procedures which returns multiple data sets (tables).

Insert Into #tt (Label, Q1, Q2, Q3, Q4, Q5) EXEC Pr_Class_Question_Dtl @Class = 1

But, how can we do this for Multiple Data Sets(Tables) in MS-SQL Server ? Please guide.

Thanks & Regards,

As far as I know, this is not possible.

The insert..exec simply tries to dump all the data into the table. If both of your datasets had exactly same number of columns and data types, then it would insert the results of both datasets into a the table.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-22 : 23:12:11

i would have done this using two separate procedures. It doesnt make sense for same procedure to return varying type of resultsets.
that being said, if your situations demands it due to some constraints, i would still make both resultsets same by putting place holders for all columns using NULL values whenever a particular column is not included in resultset and have an additional column to indicate category. this can later be used to filter out individual records from table.

so something like

EXEC Pr_Class_School_Dtl @Class = 1

Then Output is coming in 2 Data Sets (Tables): -

Data Set (Table) 1: -
Label Q1 Q2 Q3 Q4 Q5 Schools Board Category
Class I A B C D E NULL NULL Resultset1

Data Set (Table) 2: -
Label Q1 Q2 Q3 Q4 Q5 Schools Board Category
NULL NULL NULL NULL NULL NULL Outlook CBSE Resultset2
NULL NULL NULL NULL NULL NULL MS RGPV Resultset2
NULL NULL NULL NULL NULL NULL Mac Test Resultset2

Insert Into #tt (Label Q1 Q2 Q3 Q4 Q5 Schools Board Category)
EXEC Pr_Class_Question_Dtl @Class = 1

...

then for getting individual resultset

SELECT Label, Q1, Q2, Q3, Q4, Q5
FROM #tt
WHERE Category = 'Resultset1'

SELECT Schools, Board
FROM #tt
WHERE Category = 'Resultset2'
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources