T-Sql How to return a table from a storedproc in a

问题:

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

回答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.

回答2:

The temporary-table approach, at least as expressed above, didn't work for me. You can use a variable, just as easily.

DECLARE @return_value INT
DECLARE @tblOutputTable TABLE(Col1 BIT NOT NULL, Col2 INT NOT NULL)

INSERT INTO @tblOutputTable EXEC @return_value = [dbo].[SomeSp] @Param1 = 15, @Param2 = 2

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