June 23, 2006 at 4:12 am
I have searched without success for a sample of combining the results of 2 stored procedures. Basically I would like to write a stored procedure that accepts a number of parameters and passes those parameters along to two different stored procedures and returns a recordset that is the union of the results of the 2 stored procedures. Any ideas on the best way to do this?
Thanks
June 23, 2006 at 6:26 am
Create a table variable or temporary table and store the result for each store procedure. Then when done, UNION them together.
N 56°04'39.16"
E 12°55'05.25"
June 23, 2006 at 6:31 am
do you know where I could find a sample of doing that?
June 23, 2006 at 8:43 am
Here's an example...
create proc test1 as
select 1, 2
go
create proc test2 as
select 3, 4
go
create proc test3 as
create table #t (i int, j int)
insert #t exec test1
insert #t exec test2
select * from #t
go
exec test3
drop proc test1
drop proc test2
drop proc test3
/*results
i j
----------- -----------
1 2
3 4
*/
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
June 23, 2006 at 8:48 am
I forgot the parameter passing bit, so, in case it wasn't obvious...
create proc test1 @v-2 varchar(20) as
select 1, 2, @v-2 + ' world'
go
create proc test2 @v-2 varchar(20) as
select 3, 4, @v-2 + ' again'
go
create proc test3 @v-2 varchar(20) as
create table #t (i int, j int, v varchar(20))
insert #t exec test1 @v-2
insert #t exec test2 @v-2
select * from #t
go
exec test3 'hello'
drop proc test1
drop proc test2
drop proc test3
/*results
i j v
----------- ----------- --------------------
1 2 hello world
3 4 hello again
*/
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
June 23, 2006 at 8:58 am
thanks
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply