Sql – Access to Result sets from within Stored procedures Transact-SQL SQL Server

sqlsql servertsql

I'm using SQL Server 2005, and I would like to know how to access different result sets from within transact-sql. The following stored procedure returns two result sets, how do I access them from, for example, another stored procedure?

CREATE PROCEDURE getOrder (@orderId as numeric) AS
BEGIN   
    select order_address, order_number from order_table where order_id = @orderId
    select item, number_of_items, cost from order_line where order_id = @orderId
END

I need to be able to iterate through both result sets individually.

EDIT: Just to clarify the question, I want to test the stored procedures. I have a set of stored procedures which are used from a VB.NET client, which return multiple result sets. These are not going to be changed to a table valued function, I can't in fact change the procedures at all. Changing the procedure is not an option.

The result sets returned by the procedures are not the same data types or number of columns.

Best Answer

The short answer is: you can't do it.

From T-SQL there is no way to access multiple results of a nested stored procedure call, without changing the stored procedure as others have suggested.

To be complete, if the procedure were returning a single result, you could insert it into a temp table or table variable with the following syntax:

INSERT INTO #Table (...columns...)
EXEC MySproc ...parameters...

You can use the same syntax for a procedure that returns multiple results, but it will only process the first result, the rest will be discarded.

Related Topic