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

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.

I was easily able to do this by creating a SQL2005 CLR stored procedure which contained an internal dataset.

You see, a new SqlDataAdapter will .Fill a multiple-result-set sproc into a multiple-table dataset by default. The data in these tables can in turn be inserted into #Temp tables in the calling sproc you wish to write. dataset.ReadXmlSchema will show you the schema of each result set.

Step 1: Begin writing the sproc which will read the data from the multi-result-set sproc

a. Create a separate table for each result set according to the schema.

CREATE PROCEDURE [dbo].[usp_SF_Read] AS
SET NOCOUNT ON;
CREATE TABLE #Table01 (Document_ID VARCHAR(100)
  , Document_status_definition_uid INT
  , Document_status_Code VARCHAR(100) 
  , Attachment_count INT
  , PRIMARY KEY (Document_ID));

b. At this point you may need to declare a cursor to repetitively call the CLR sproc you will create here:

Step 2: Make the CLR Sproc

Partial Public Class StoredProcedures
    <Microsoft.SqlServer.Server.SqlProcedure()> _
    Public Shared Sub usp_SF_ReadSFIntoTables()

    End Sub
End Class

a. Connect using New SqlConnection("context connection=true").

b. Set up a command object (cmd) to contain the multiple-result-set sproc.

c. Get all the data using the following:

    Dim dataset As DataSet = New DataSet
    With New SqlDataAdapter(cmd)
        .Fill(dataset) ' get all the data.
    End With
'you can use dataset.ReadXmlSchema at this point...

d. Iterate over each table and insert every row into the appropriate temp table (which you created in step one above).

Final note: In my experience, you may wish to enforce some relationships between your tables so you know which batch each record came from.

That's all there was to it!

~ Shaun, Near Seattle

There is a kludge that you can do as well. Add an optional parameter N int to your sproc. Default the value of N to -1. If the value of N is -1, then do every one of your selects. Otherwise, do the Nth select and only the Nth select.

For example,

if (N = -1 or N = 0)
    select ...

if (N = -1 or N = 1)
    select ...

The callers of your sproc who do not specify N will get a result set with more than one tables. If you need to extract one or more of these tables from another sproc, simply call your sproc specifying a value for N. You'll have to call the sproc one time for each table you wish to extract. Inefficient if you need more than one table from the result set, but it does work in pure TSQL.

Note that there's an extra, undocumented limitation to the INSERT INTO ... EXEC statement: it cannot be nested. That is, the stored proc that the EXEC calls (or any that it calls in turn) cannot itself do an INSERT INTO ... EXEC. It appears that there's a single scratchpad per process that accumulates the result, and if they're nested you'll get an error when the caller opens this up, and then the callee tries to open it again.

Matthieu, you'd need to maintain separate temp tables for each "type" of result. Also, if you're executing the same one multiple times, you might need to add an extra column to that result to indicate which call it resulted from.

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

Related

Recent Posts