title: Navigating the results of a stored procedure via a cursor using T-SQL
body: <p>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.</p>
  
  <p>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.</p>
  
  <p>Does anyone know of a way to get the result set from a stored procedure into a cursor?</p>
  
  <p>I tried the obvious:</p>
  
  <pre><code>Declare Cursor c_Data For my_stored_proc @p1='foo', @p2='bar'
  </code></pre>
  
  <p>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:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>Any thoughts? Does anyone know of any other way to traverse the result set from a stored proc via a cursor?</p>
  
  <p>Thanks.</p>