Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

how to combine multiple stored procedures Expand / Collapse
Author
Message
Posted Wednesday, December 19, 2012 11:09 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Yesterday @ 2:56 AM
Points: 56, Visits: 481

Hi all


I have two stored procedures which return dynamic columns as result sets.
is there any way to combine both resultsets???????


after searching for solution i got to know that it is possible when stored procedure returns static columns by using table variable...but what about dynamic columns???????

Thanks
----------
$w@t
Post #1398784
Posted Wednesday, December 19, 2012 11:56 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, September 19, 2013 4:30 AM
Points: 78, Visits: 195
$w@t (12/19/2012)

Hi all


I have two stored procedures which return dynamic columns as result sets.
is there any way to combine both resultsets???????


after searching for solution i got to know that it is possible when stored procedure returns static columns by using table variable...but what about dynamic columns???????

Thanks
----------
$w@t


Please elaborate by an example what are you looking for.
Post #1398798
Posted Thursday, December 20, 2012 12:24 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, April 28, 2015 4:44 AM
Points: 55, Visits: 167
create temporary tables using dynamic query base on the resultset. Temporary tables are accessible in another SP and from that SP, you can join the two tables.
Post #1398821
Posted Thursday, December 20, 2012 12:32 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, September 19, 2013 4:30 AM
Points: 78, Visits: 195
eklavu (12/20/2012)
create temporary tables using dynamic query base on the resultset. Temporary tables are accessible in another SP and from that SP, you can join the two tables.


Sorry mate but You can not use temporary tables in other SP.
Post #1398824
Posted Thursday, December 20, 2012 2:32 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, April 28, 2015 4:44 AM
Points: 55, Visits: 167
sorry for the confusion. What i mean is even if they are on different SP, as long as they are within the session. It is possible to access the temporary tables.

Try to store the resultset of your SP in a temporary table. then using the query below, you can identify the columns of the resultset of your two SP's

here is the query:
select * from tempdb.dbo.syscolumns a inner join tempdb.dbo.sysobjects b on a.id = b.id
where b.name like '%#TEMP_TABLE%'

Fetch all the columns and do some logic to create dynamic sql to build your tables.

Now you have the tables. Insert the data from the resultset of the two SP and join the tables.
I Hope you understand my explanation





Post #1398873
Posted Thursday, December 20, 2012 8:37 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:21 PM
Points: 14,065, Visits: 13,681
$w@t (12/19/2012)

Hi all


I have two stored procedures which return dynamic columns as result sets.
is there any way to combine both resultsets???????


after searching for solution i got to know that it is possible when stored procedure returns static columns by using table variable...but what about dynamic columns???????

Thanks
----------
$w@t


If you know what the columns will be you can insert the results of each proc into a table assuming the columns are the same in both procs. I have a feeling that the number and datatypes of columns are not consistent from these two procs. If that is the case you are out of luck. As has been said in this thread and the other duplicate thread, you need to provide some details about what you are trying to do.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1399013
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse