title: Getting summary of results from another stored procedure
body: <p>I have one complex stored procedure that returns some rows with some calculated values</p>
  
  <pre><code>SELECT  CalculatedField1 ,
          CalculatedField2 ,
          ...
  FROM ...
  WHERE CONDITION
  </code></pre>
  
  <p>this sproc (lets call it procA) returns variable number of rows, depending on the WHERE condition. This works fine. What I need to do now is to write a stored procedure that will get the summary of these returned rows.</p>
  
  <pre><code>-- procB
  SELECT SUM(CalculatedField1),
  SELECT SUM(CalculatedField2),
         ...
  FROM (EXEC procA params)
  </code></pre>
  
  <p>Is this possible?</p>
  
  <p>Edit: creating a temp table did the job, however I have problems with passing output pareters.</p>
  
  <pre><code>CREATE PROCEDURE [dbo].[sprocB] (@prm INT = NULL OUTPUT)
  AS
  BEGIN
  SET NOCOUNT ON;
  
      SET @prm = 1
  
      SELECT Id FROM dbo.AnyTable
  END
  
  CREATE PROCEDURE [dbo].[sprocA] (@prm INT = NULL OUTPUT)
  AS
  BEGIN
  SET NOCOUNT ON;
  
      CREATE TABLE #temp (Id INT)
  
      INSERT INTO #temp
      EXEC sprocB @prm
  
      SELECT Id FROM #temp
  END
  </code></pre>
  
  <p>To execute:</p>
  
  <pre><code>USE [MyDatabase]
  GO
  
  DECLARE @return_value int,
      @prm int
  
  EXEC    @return_value = [dbo].[sprocA]
      @prm = @prm OUTPUT
  
  SELECT  @prm as N'@prm'
  
  SELECT  'Return Value' = @return_value
  
  GO
  </code></pre>
  
  <p>The results from temp table are ok, resultset is retrieved correctly, however @pem value is still NULL.</p>