Two stored procedure for only one recordset 1

[gvielliard]

Hi all,
I have two stored procedure StoredProc1 and StoredProc2 runing on two different servers, they are identical, only the datas in the recordset they retrieve are different.
The two recordset have the same structure (same field names).
What I want to have is only one recordset but I can't mofify anything inside StoredProc1 and StoredProc2 and depending of the option entered in StoredProc1 and StoredProc2, the number and the name of the fields are different.
If something like UNION between two stored procedure exists it would solve the problem.

To be clearer, here is an example:
If option 1 is set (for StoredProc1 and StoredProc2) I have this 2 recordset
ProductID ClientID Price
--------- -------- -----
10000 10000 10
10001 10001 11
10002 10002 12
ProductID ClientID Price
--------- -------- -----
20000 20000 20
20001 20001 21

and what I need is this recorset (a merge of the two previous)
ProductID ClientID Price
--------- -------- -----
10000 10000 10
10001 10001 11
10002 10002 12
20000 20000 20
20001 20001 21

If the Option is set to 2, I would have these 2 recordset:
ClientID Location
-------- --------
10000 City10
10001 City11
10002 City12
10003 City13
10004 City14
ClientID Location
-------- --------
20000 City20
20001 City21

And I want this single recordset:
ClientID Location
-------- --------
10000 City10
10001 City11
10002 City12
10003 City13
10004 City14
20000 City20
20001 City21

Thank you for youe help.
Gilles

 

[tlbroadbent]

If each of the stored procedures resturns a records set, you can insert the records into a temporary table and then select records from the temp table for the final result set.

Example:

If @option = 1
Begin
Create table #tmp (ProductID int, ClientID int, Price int)
End
Else
Begin
Create table #tmp (ClientID int, Location varchar(6))
End

Insert #tmp
Exec StoredProc1 @option
Insert #tmp
Exec StoredProc2 @option

Select * From #tmp

Drop Table #tmp Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[gvielliard]

Thank you Terry, I didn't think about this, you've been a great help for me
Gilles

 

[gvielliard]

Terry,
Actually, I tested your programm, it doesn't work, you have to create a temp table for each option otherwise you will have this error:
There is already an object named '#tmp' in the database

This will work:

If @option = 1
Begin
Create table #tmp1 (ProductID int, ClientID int, Price int)

Insert #tmp1
Exec StoredProc1 @option
Insert #tmp1
Exec StoredProc2 @option

Select * From #tmp1

Drop Table #tmp1
End
Else
Begin
Create table #tmp2 (ClientID int, Location varchar(6))

Insert #tmp2
Exec StoredProc1 @option
Insert #tmp2
Exec StoredProc2 @option

Select * From #tmp2

Drop Table #tmp2
End


Gilles

 

[BasSchouten]

insert #temp
  EXEC StoredProc1 @Option

Does this work?? Well thats a new one for me ... wish I knew this one before, it would have simplified some of the procedures I'v created

Thanks! Bas Schouten
System Development & Webdesign
CBIS BV Holland