Using Union All in a Stored Procedure

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

SQL Server Development (2000)

Using Union All in a Stored Procedure

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-07-03 : 07:42:22

Dan writes "I've got 5 stored procedures and I'd like to create a single stored procedure that calls them all and returns the data in a single recordset, like a "Union All" query would. For insance, "usp_1" returns all residents of Virginia, "usp_2" returns all residents of Maryland, and so on... then finally "usp_all" returns all residents from all 5 stored procs.

What's the best way to do this? Create a temp table and insert data as each sp is called? Use the "Union All" keywords?

Thanks in advance!"

pnbushan
Starting Member

1 Post

Posted - 2003-07-03 : 08:37:27

Hi,
If ur SPs does contain any Dynamic SQL/DML statements, try to convert them into user defined functions and try the following.

Select * from dbo.UFn_1
UNION ALL
Select * from dbo.UFn_2
UNION ALL
Select * from dbo.UFn_3
UNION ALL
Select * from dbo.UFn_4
UNION ALL
Select * from dbo.UFn_5

Thanks
Bushan

- Bushan

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-07-03 : 10:11:24

Read up more on stored procedures and database fundamentals. You should not be using stored procedures or views or UDF's in this manner. You should NEVER be altering/creating/deleting database OBJECTS depending on the data that is stored. The objects themselves should accept parameters if users wish to fitler for specific sets of data, the objects themselves should NOT be hardcoded and created redundantly to do this filtering.

What happens as states are added to your database? eventually you will have 50 stored procedures (all with vague names, by the way)?

You need to create 1 stored procedure that accepts an argument of which state you would like to return, or maybe you can pass no argument or a dummy argument of 'ALL' and it returns all states (w/o needing to a UNION of multiple stored procs, to relate this to your original question).

This is kind of hard to explain, maybe someone can help clarify ... but please don't use stored procedures this way!

- Jeff

Edited by - jsmith8858 on 07/03/2003 11:50:39

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources