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

Question

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

Answer 1

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.

Answer 2

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

Answer 3

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.)

Answer 4

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