Stored procedures and Union - SQL Server Forums

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)

Stored procedures and Union

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-11-02 : 07:46:56

libby writes "Hey,

I have three stored procedures which take the same set of parameters but get the data from three different tables. My requirement is to join these three procedures by sending the same set of parameters and getting one output.

I tried to use UNION

create procedure sp1
(
@param1 varchar(5),
@param2 varchar(5),
@param3 varchar(5)
)

AS

BEGIN

EXEC sp_one @param1, @param2, @param3

UNION

EXEC sp_two @param1, @param2, @param3

UNION

EXEC sp_three @param1, @param2, @param3

END
GO

But this doesnt work. I get a syntax error near UNION.

Please help..

Thanks.

Its sqlserver 2000, windows xp service pack2."

nr
SQLTeam MVY

12543 Posts

Posted - 2006-11-02 : 08:00:00

set nocount on
create table #a(...)
insert #a
EXEC sp_one @param1, @param2, @param3
insert #a
EXEC sp_two @param1, @param2, @param3
insert #a
EXEC sp_three @param1, @param2, @param3

select * from #a
drop table #a

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Kristen
Test

22859 Posts

Posted - 2006-11-02 : 08:01:31

Create a temporary table with appropriate columns for the result set from the SProcs then:

INSERT INTO #MyTempTable
EXEC sp_one @param1, @param2, @param3

INSERT INTO #MyTempTable
EXEC sp_two @param1, @param2, @param3

INSERT INTO #MyTempTable
EXEC sp_three @param1, @param2, @param3

SELECT *
FROM #MyTempTable

Kristen

nr
SQLTeam MVY

12543 Posts

Posted - 2006-11-02 : 08:02:53

lol - think that's one each Kristen

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Kristen
Test

22859 Posts

Posted - 2006-11-02 : 08:06:02

I couldn't see khtan or peso around, so I jumped in - I need the post count!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-11-02 : 20:22:17

quote:
Originally posted by Kristen

I couldn't see khtan or peso around, so I jumped in - I need the post count!

Madhivanan

Failing to plan is Planning to fail

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-11-02 : 22:21:59

The solutions above are more like a UNION ALL than a UNION.

This would be more like a UNION:


create table #a(...)
create table #b(...)
create table #c(...)
insert #a
EXEC sp_one @param1, @param2, @param3
insert #b
EXEC sp_two @param1, @param2, @param3
insert #c
EXEC sp_three @param1, @param2, @param3

select * from #a union
select * from #b union
select * from #c

Just pumping my post count.

CODO ERGO SUM

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-11-02 : 22:50:00

quote:
Originally posted by Kristen

I couldn't see khtan or peso around, so I jumped in - I need the post count!

Hey I am in diff time zone. I am here now

"I need the post count!"

"Just pumping my post count."
Can i join in

Kristen
Test

22859 Posts

Posted - 2006-11-03 : 06:45:29

"This would be more like a UNION:"

Personally I would prefer a single #TempTable and a SELECT DISTINCT for the results, rather than multiple #TempTables.

Horses for Course though ...

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources