Combine two stored procedure results

Question

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

Answer 1

Can you change getdata (or is this something your stuck with?)

Another possible option is to turn your getdata stored procedure into a table valued function.  This means you won't be able to exec it directly though, you will have to access it in a query.  Then you could do:

select * from db2006.dbo.getdata(...) union select * from db2005.dbo.getdata(...).

This would most likely perform better (although I can't say for sure).  If getdata() is one query (or can be turned into one query), then you can use an inline table valued function, and this will definitely perform better then the temp table solution.

Also, your probably better off using a table variable in this case as opposed to a temp table.

 

Answer 2

if you are stuck with the stored procedure as-is, then your idea is the best one.  Unless the proc is extremely complex (and with a name like getdata, it is not going to be easy for us to guess :) then building a database for queries that span databases is a better idea and union the results together.  Or consider the ideas that Adam has given also.

I would consider not having databases with the year in the name, and just have one database that spans years, personally.  That is clearly the most solid answer and will make your reporting easier.  If this wasis a performance idea, there are ways to make this work far better than with multiple databases.  And if both databases are on the same drive, you are possibly not saving much...

Answer 3

Thank you very much for your ideas.For the reports the stored procedures already created.But now to imrove the performance they created the separate 3 databases one for current year and other for previous year and remaining(all previous years are in Hist databases).Now I need to migrate the existing stored procedure to all databases all working fine but the problem is when they enter startdate which is in one year and end date in another year, in this case we need to combine the results of two stored procedures from two databases.Thatswhy I created a separate stored procedure and temp table is used for combining the two SP results.

Thanks

 

Answer 4

Hi,

 Which one give better performance whether the Stored procedure with table datatype to insert the combined results from two databases, Or table valued functions.

Thanks.

Answer 5

Hi mahima i also found same solution as define in http://codingresolved.com/discussion/73/how-to-union-two-stored-procedures-results-in-mssql-2005