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
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, March 5, 2014 10:24 PM
Points: 48, Visits: 370

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: Monday, June 16, 2014 5:32 AM
Points: 55, Visits: 158
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: Monday, June 16, 2014 5:32 AM
Points: 55, Visits: 158
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:34 PM
Points: 12,923, Visits: 12,342
$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
Posted Friday, December 21, 2012 8:55 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 5:15 PM
Points: 1,945, Visits: 3,008
I have two stored procedures which return dynamic columns as result sets.


Please read a book on Software Engineering. Look up the concept of "cohesion" and learn it. Regardless of the progamming language, a well-srtructured, properly written module of code has (1) One entry point (2) one exit point and (3) performs one clearly defined task. The strongest cohesion is "functional"; think of a math function. It is so important that we have functional progamming languages .

Your narrartive describes a "Automobiles, Squids and Lady Gaga" module whose output varies insanely. Columns built rows, rows build tables, tables model entities; ever-chaigning columns become ever-changing entities. This is why we mock bad SQL with "Automobiles, Squids and Lady Gaga", but it appleis to any language.

is there any way to combine both resultsets?


How do we answer that question without code? Then quick answer is to throw the automobiles or squids or whatever from query #1 into a temp table (aka "garbage bag", "scratch tape", etc) and "leave a note" on it for query #2 so it knows what it is getting.

This garbage bag leads to the second fundamental concept of Software Engineering; "coupling", or how modules work together. We want loose coupling. Think Lego blocks. The worst kind of coupling locks two modules together so that one module has to know about the other before it can do anything.

Google the terms and read. There are some nice short articles on-line. You might want to look at your old code, too. It probably has the same flaws.



Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #1399463
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse