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

Bela

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.

Glassman

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 – Incident

Answer 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

Schwab

Answer 3

The INSERT\EXEC pattern has issues (not listed here - google it), so if you choose that path make sure your happy with those caveats.

My goto pattern for this kind of thing (and is usually part of some large data processing) is to create a known temp table structure. In SQL Server you can access a temporary table in a child procedure that is defined in a parent.

So this is a typical example :

CREATE PROCEDURE [dbo].[ChildProc] 
AS BEGIN

IF OBJECT_ID('tempdb..#JsonDataBatch') is null BEGIN
    PRINT 'ChildProc Stored Procedure is designed to called from another stored proc defining #JsonDataBatch temp table';
    THROW 61000, 'Stored Procedure is designed to called from another stored proc (Missing #JsonDataBatch temp table)',1

    CREATE TABLE #JsonDataBatch  (
        RecordId bigint NOT NULL,
        JSONData varchar(max) NOT NULL,
        PRIMARY KEY CLUSTERED (RecordId)
    )

END

INSERT INTO #JsonDataBatch (RecordId, JSONData) 
VALUES (1, '{}'),
(2, '{}'),
(3, '{}'),
(4, '{}');

END
GO

CREATE PROCEDURE [dbo].[ParentProc] 
AS BEGIN

CREATE TABLE #JsonDataBatch  (
        RecordId bigint NOT NULL,
        JSONData varchar(max) NOT NULL,
        PRIMARY KEY CLUSTERED (RecordId)
    )

EXEC [dbo].[ChildProc] 

-- #JsonDataBatch table now has data
SELECT * FROM #JsonDataBatch
END
GO

EXEC [dbo].[ParentProc] 

The IF Block at the top of the child procedure isn't strictly necessary for this pattern to work, however it serves two purposes :

1. To document the behavior and ensure the child procedure is being called correctly
2. So that intellisence in SQL Server Management Studio works.

Perrins