Navigating the results of a stored procedure via a cursor using T-SQL

Question

Due to a legacy report generation system, I need to use a cursor to traverse the result set from a stored procedure. The system generates report output by PRINTing data from each row in the result set. Refactoring the report system is way beyond scope for this problem.

As far as I can tell, the DECLARE CURSOR syntax requires that its source be a SELECT clause. However, the query I need to use lives in a 1000+ line stored procedure that generates and executes dynamic sql.

Does anyone know of a way to get the result set from a stored procedure into a cursor?

I tried the obvious:

Declare Cursor c_Data For my_stored_proc @p1='foo', @p2='bar'

As a last resort, I can modify the stored procedure to return the dynamic sql it generates instead of executing it and I can then embed this returned sql into another string and, finally, execute that. Something like:

Exec my_stored_proc @p1='foo', @p2='bar', @query='' OUTPUT
Set @sql = '
    Declare Cursor c_Data For ' + @query + '
    Open c_Data
    -- etc. - cursor processing loop etc. goes here '
Exec @sql

Any thoughts? Does anyone know of any other way to traverse the result set from a stored proc via a cursor?

Thanks.

Solution 1

You could drop the results from the stored proc into a temp table and select from that for your cursor.

CREATE TABLE #myResults
(
    Col1 INT,
    Col2 INT
)

INSERT INTO #myResults(Col1,Col2)
EXEC my_Sp

DECLARE sample_cursor CURSOR
FOR
 SELECT
    Col1,
    Col2
 FROM
    #myResults

Another option may be to convert your stored procedure into a table valued function.

DECLARE sample_cursor CURSOR
FOR
  SELECT
     Col1,
     Col2
  FROM
     dbo.NewFunction('foo', 'bar')

Solution 2

You use INSERT ... EXEC to push the result of the procedure into a table (can be a temp #table or a @table variable), the you open the cursor over this table. The article in the link discusses the problems that may occur with this technique: it cannot be nested and it forces a transaction around the procedure.

Comments

[Chris Judge]

The INSERT INTO ... EXEC ... syntax should do the trick. Thanks! Can't use the table-valued function - the legacy system is using compatibility-level 6.5.