T-Sql How to return a table from a storedproc in another stored proc.

I would like to do the following. Basically have a stored procedure call another stored procedure that returns a table. How is this done?

    ALTER PROC [GETSomeStuff]
    AS
    BEGIN

    @table = exec CB_GetLedgerView @accountId, @fromDate, @toDate, @pageSize, @pageNumber, @filter, @status, @sortExpression, @sortOrder, @virtualCount OUTPUT

   Select * from @table
   --Do some other stuff here        
    END

From stackoverflow

• The target of a stored procedure has to be a temp or actual table so you can

      Insert into #table exec CB_GetLedgerView @accountId, @fromDate, 
  @toDate, @pageSize, @pageNumber, 
  @filter, @status, @sortExpression, 
  @sortOrder, @virtualCount OUTPUT
  

  If the output result set of the stored procedure does not match the ordinal positions and count of the rows in the target table, specify a column list.

  Arron : simple and awesome! Thanks

  cmsjr : glad I could help.

  Unsliced : The exception to this is that if you have an insert into a temporary table within the sproc, then you cannot then insert its output into another temp table

• First you need to create a virtual table with the schema returned. Next populate the table from the results of the stored procedure. last use the temp table in joins or add where clauses to it to filter the data you want. You can also set up a cursor to go through the temp table.

  DECLARE @TABLE TABLE ( Var1 int, var2 int, etc ... ) 
  EXECUTE @TABLE = spReturnTable Parm1, Parm2, etc... 
  SELECT * FROM @TABLE
  

• Maybe your example isn't really representative, but the first question I'd have have is, do you really need to make this two procedures, at the cost of greater complexity? Decomposition like this is somewhat of an antipattern with SQL. (Although some will disagree, but I've seen this discussed with majority agreement here on SO.)

• SQL Server 2008 allows you to use tables as parameters.

  Example from [http://itknowledgeexchange.techtarget.com/sql-server/just-how-awesome-are-table-parameters-in-sql-server-2008/][1]:

  CREATE TYPE MyTableType AS TABLE
     (Id INT)
  GO
  CREATE PROCEDURE MyProcedure
     @Ids MyTableType OUTPUT
  AS
  INSERT INTO @Ids
  SELECT object_Id
  FROM sys.objects
  GO
  DECLARE @values MyTableType
  exec MyProcedure @Ids=@values OUTPUT
  SELECT *
  FROM @values
  GO
  DROP PROCEDURE MyProcedure
  GO
  DROP Type MyTableType
  GO