Combining resultset of many stored procedures

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 2000 Forums

Transact-SQL (2000)

Combining resultset of many stored procedures

Author

Topic

jbolduc81
Starting Member

6 Posts

Posted - 2005-12-05 : 15:32:05

Hi,

I have multiple queries to do in different tables, all these queries have the same output fields.

If I want to combine these queries with a UNION clause in the same stored procedure, I can do it, but I don't want to. I would like to know if it is possible to have these queries in separate stored procedures and use another strored procedure to return the resultset of all the other stored procedures combined.

This is an exemple of what i want to do but it doesn't work:
MyStoreProc1(Param1, Param2)
UNION
MyStoreProc2(Param1, Param2)
UNIOIN
MyStoreProc3(Param1, Param2)

thank you for your help

druer
Constraint Violating Yak Guru

314 Posts

Posted - 2005-12-05 : 15:52:39

I know that one way to do it would be to create temp tables for your result sets and then

insert into #temp1
EXEC(sp_myproc1) ---or--- sp_executesql

insert into #temp2 etc

Then do a UNION ALL from the three temp tables. You could also convert the stored procedures to User Defined Functions perhaps and then simply have them return a TABLE variable with the results. Not sure if you can just do

EXEC(sp_myproc1)
UNION ALL
EXEC(sp_myproc2)

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-12-05 : 15:58:12

Chances are you have a bad design here ... can you give us more specific information about why you have many stored procedures that essentially return similiar results?

jbolduc81
Starting Member

6 Posts

Posted - 2005-12-05 : 16:23:40

quote:
Originally posted by jsmith8858

Chances are you have a bad design here ... can you give us more specific information about why you have many stored procedures that essentially return similiar results?

I totally agree with you that it is a bad design, all these datas should have been saved in the same tables, but i have to work with it now...

Is there any system function or other way to combine resultsets of many stored procedures ?

Temp tables are maybe the solution, can I get an example somewhere ?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-12-06 : 01:45:51

In your case apply the method suggested by druer

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources