Combing two stored procedure results

[kalkumar]

Hi,
I have two databases DB2006, DB2005.I have the Stored Procedure getdata which has the 2 parameters startdate and end date.This Stored procedure exist in all databases.

STored procedure CallGetdata
@startdate datetime
@enddate datetime
If startdate < 1/1/2007 the call getdata in the DB2006
if startdate <1/1/2006 then call getdata in the DB2005.
Here the problem is if startdate is 6/1/2005 and Enddate is '3/1/2006' then combine the stored procedure results from the DB2006 and DB2005 databases.
I have one idea i.e create a temp table and insert the two Stored procedure results into it.
Create #table1(name varchar(20))
insert into #table1 exec DB2006.dbo.getdata
insert into #table1 exexc DB2005.dbo.getdata
Select * from #table1
drop table #table1.
Anyone please give me better idea than creating temp table.

Thanks in advance

 

[gmmastros]

You may want to consider creating a view in each database that effectively unions the data from the table(s) that you care about. Then, modify the stored procedure to use the view instead of the table.

Really, what I am suggesting is called Horizontal Partitioning.

http://www.sqlteam.com/Item.ASP?ItemID=684

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom