how to combine multiple stored procedures

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server Newbies » how to combine multiple stored procedures

how to combine multiple stored procedures

Rate Topic

Display Mode

Topic Options

Author

Message

$w@t

Posted Wednesday, December 19, 2012 11:09 PM

Grasshopper

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 5:02 AM
Points: 11, Visits: 152

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

rhythm.varshney

Posted Wednesday, December 19, 2012 11:56 PM

SSC Journeyman

Group: General Forum Members
Last Login: Thursday, March 21, 2013 2:14 AM
Points: 77, Visits: 184

$w@t (12/19/2012)

Please elaborate by an example what are you looking for.

Post #1398798

eklavu

Posted Thursday, December 20, 2012 12:24 AM

Valued Member

Group: General Forum Members
Last Login: Today @ 1:33 AM
Points: 51, Visits: 146

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

rhythm.varshney

Posted Thursday, December 20, 2012 12:32 AM

SSC Journeyman

Group: General Forum Members
Last Login: Thursday, March 21, 2013 2:14 AM
Points: 77, Visits: 184

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

eklavu

Posted Thursday, December 20, 2012 2:32 AM

Valued Member

Group: General Forum Members
Last Login: Today @ 1:33 AM
Points: 51, Visits: 146

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 Smile

Post #1398873

Sean Lange

Posted Thursday, December 20, 2012 8:37 AM

SSCrazy Eights

Group: General Forum Members
Last Login: Today @ 2:10 PM
Points: 8,592, Visits: 8,232

$w@t (12/19/2012)

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

Post #1399013

CELKO

Posted Friday, December 21, 2012 8:55 AM

SSCommitted

Group: General Forum Members
Last Login: Tuesday, January 15, 2013 11:11 AM
Points: 1,945, Visits: 2,782

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 »

Permissions