Joining output data of two T-SQL Stored Procedures from an SP

Question

Right now I have two stored procedures that return data sets, and I'd like to create another stored procedure that executes both stored procedures and returns their combined datasets (to a .Net application).

Is this as simple as just running "EXEC" on both of the stored procedures or do I need to add in some logic that combines the two data sets?

Answer 1

You can just exec each SP one after the other and the application ('m assuming it's based on .NET) will see two result-sets. The results will not be combined into a single result though so you'll need to use DbDataReader.NextResult(): http://msdn.microsoft.com/en-us/library/system.data.common.dbdatareader.nextresult.aspx.

If you need the results to be combined into a single result as far as the application is concerned you'll need to insert the results of th SPs into two table-valued-variables and then SELECT-JOIN them.

Alternatively, and if possible, convert the two child SPs into table-valued functions and then SELECT-JOIN the results directly.