title: Retrieve data from stored procedure which has multiple result sets
body: <p>Given a stored procedure in SQL Server which has multiple <code>select</code> statements, is there a way to work with those results separately while calling the procedure?</p>
  
  <p>For example:</p>
  
  <pre><code>alter procedure dbo.GetSomething
  as
  begin
      select * from dbo.Person;
      select * from dbo.Car;
  end;
  </code></pre>
  
  <p>In .NET, if I call this proc, I can use a <code>SqlDataReader</code> to move between the two result sets, so I can easily retrieve all people and cars. In SQL however, when I execute the proc directly, I get both result sets.</p>
  
  <p>If I call:</p>
  
  <pre><code>insert @myTempTable
      exec dbo.GetSomething;
  </code></pre>
  
  <p>Then it errors because the column definition doesn't match. If by some chance Person and Car have the same columns, it concatenates the two together, and @myTempTable gets all records from both tables, which obviously is no good either.</p>
  
  <p>I can define new custom types representing the two result sets, and make those output parameters instead of having the multiple <code>select</code> statements, but I'm wondering if there's a better way - some way of pulling both results into temporary tables, or looping through the results, or something.</p>
  
  <p><strong>EDIT</strong></p>
  
  <p>Actually, after looking more closely, even output table parameters won't solve this - they're readonly, and that's still true in SQL 2012. (<a href="https://connect.microsoft.com/SQLServer/feedback/details/299296/relax-restriction-that-table-parameters-must-be-readonly-when-sps-call-each-other">Connect ticket asking for this to be added</a>)</p>