title: SSRS - return multiple queries in one stored procedure
body: <p>I am attempting to create a new SSRS report that will return and display the values of a SQL Server stored procedure. I will pass a parameter <code>@clientID</code> to the stored procedure. This parameter is used in 3 different <code>BEGIN</code>/<code>END</code> statements. Each <code>BEGIN</code>`END` statement takes the parameter and makes a query, returning specific data.  </p>
  
  <p>When I create the SSRS report, I point the datasource to this stored procedure, but only the result set from the first <code>BEGIN</code>/<code>END</code> statement is returned. If I run the stored procedure in SSMS, I get 3 different result sets, as expected.  </p>
  
  <p>How can I get those 3 <code>BEGIN</code>/<code>END</code> result sets into a single report?  </p>
  
  <p>Sample code:</p>
  
  <pre><code>CREATE PROCEDURE pClientData (@clientID varchar(30))
  AS
  
      DECLARE @Orders table (
              ...
              ); 
  
      DECLARE @Results table (
              ...
              );
  
      DECLARE @Status table (
              ...     
              );
  
      BEGIN
          SET NOCOUNT ON;
  
          -- Get all the orders by client
          INSERT INTO @Orders
          SELECT ...
  
  
          -- Return the results --
          SELECT *
          FROM @Orders;
  
      END
  
      BEGIN
          SET NOCOUNT ON;
  
          -- Determine the Results
  
          INSERT INTO @Results
          SELECT ...
  
          SELECT * 
          FROM @Results;
  
      END
  
      BEGIN
          SET NOCOUNT ON;
  
          SET @Status = (
          SELECT ...
          );
  
          SELECT @Status as Status;
  
      END
      GO
  </code></pre>
  
  <p>Query call from SSRS:</p>
  
  <pre><code>EXEC pClientData @clientID
  </code></pre>