Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


how to combine multiple stored procedures


how to combine multiple stored procedures

Author
Message
$t@r
$t@r
Valued Member
Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)

Group: General Forum Members
Points: 65 Visits: 628
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
rhythm.varshney
rhythm.varshney
SSC Journeyman
SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)

Group: General Forum Members
Points: 88 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.
eklavu
eklavu
Valued Member
Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)

Group: General Forum Members
Points: 62 Visits: 173
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
rhythm.varshney
SSC Journeyman
SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)

Group: General Forum Members
Points: 88 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.
eklavu
eklavu
Valued Member
Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)

Group: General Forum Members
Points: 62 Visits: 173
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
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16626 Visits: 17024
$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)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search