CODEWITHSUNDEEP
t-sqlstored-procedures
Arron S
Arron S

Reputation: 5537

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

Upvotes: 15

Views: 45257

Answers (3)

Adam Hardy
Adam Hardy

Reputation: 426

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.

Upvotes: 1

Dustin Oprea
Dustin Oprea

Reputation: 10236

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

Upvotes: 5

cmsjr
cmsjr

Reputation: 59165

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.

Upvotes: 22

Related Questions