how to combine multiple stored procedures

Question

how to combine multiple stored procedures

$t@r

SSCommitted

Points: 1751
More actions
December 19, 2012 at 11:09 pm

#286638

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

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

rhythm.varshney Ten Centuries Points: 1138 More actions · Answer 1

$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.

Eklavu Right there with Babe Points: 790 More actions · Answer 2

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.

rhythm.varshney Ten Centuries Points: 1138 More actions · Answer 3

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.

Eklavu Right there with Babe Points: 790 More actions · Answer 4

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 🙂

Sean Lange SSC Guru Points: 286590 More actions · Answer 5

$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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/