SQL Server 2005 joining results of two different sp like ...

Question

Hi, I want to join results of two different store procedures that returns same structure in following fashion:

   EXEC StoreProcedure1 p1
   UNION
   EXEC StoreProcedure2 p2

I realize that is not possible, can sombody suggest elegant alternative?

I beleive I should use temp table ?

Thanks

Answer 1

You could convert the stored procedures into user defined functions that return tables instead and do:

SELECT * FROM dbo.MyFunction1(p1) 
UNION
SELECT * FROM dbo.MyFunction2(p2)

Answer 2

In SQL Server you could do the following:

create table #temp (
  col1 type,
  col1 type,
  ...
)

insert into #temp
exec sproc1
go
insert into #temp
exec sproc2
go

select * from #temp
go

drop table #temp

However, my preference would be to determine which queries both stored procedures are running and then write a query from scratch performing the same queries and unioning them together.