INSERT EXEC: Capturing a Procedure’s Result Set and Its Limits

Capture a procedure's returned rows when another batch needs to join or validate them. INSERT EXEC captures that output, provided the result contract and the entire call chain satisfy its limits.

Give the Procedure a Stable Result Contract

A result set has ordered columns with data types, lengths, precision, scale, and nullability. The capture table must accept that ordered shape. Matching column names alone does not establish compatibility.

Create the following demonstration objects in an isolated test database. The procedure returns one explicit two-column result set. SET NOCOUNT ON suppresses row-count messages without changing the selected data.

CREATE TABLE dbo.ResultItem
(
    ItemId int NOT NULL PRIMARY KEY,
    ItemCode varchar(20) NOT NULL
);
INSERT dbo.ResultItem(ItemId, ItemCode)
VALUES (1, 'A100'), (2, 'A200'), (3, 'A300');
GO
CREATE OR ALTER PROCEDURE dbo.ResultItems
    @MinimumId int
AS
BEGIN
    SET NOCOUNT ON;
    SELECT ItemId, ItemCode
    FROM dbo.ResultItem
    WHERE ItemId >= @MinimumId;
END;
GO

I inspect every result-producing SELECT before capturing a procedure. I also check conditional branches and helper procedures in its call chain. An extra diagnostic SELECT can change a public result contract without changing the main query.

PRINT messages and row-count messages are not additional tabular result sets. A SELECT that returns diagnostic text is an additional result set. Keep those differences clear when troubleshooting an apparent shape mismatch.

Run INSERT EXEC into a Temporary Table

Create the destination explicitly and list its receiving columns. SQL Server matches procedure output by position to that column list. Apply sorting and additional filtering after the capture, where normal relational queries are available.

DROP TABLE IF EXISTS #CapturedItems;
CREATE TABLE #CapturedItems
(
    ItemId int NOT NULL,
    ItemCode varchar(20) NOT NULL
);
INSERT #CapturedItems(ItemId, ItemCode)
EXEC dbo.ResultItems @MinimumId = 2;

SELECT ItemId, ItemCode
FROM #CapturedItems
WHERE ItemCode LIKE 'A%'
ORDER BY ItemId;

The destination column names can differ from the source names, but the order still controls the mapping. Compatible conversions are possible, yet narrower lengths can reject data. Keep the capture definition aligned with the intended contract instead of relying on accidental conversion.

This capture does not let you append an ordinary WHERE clause to the procedure invocation. Capture first and query the destination afterward. A procedure parameter is the appropriate way to request supported source filtering.

A return code and output parameters are separate channels from the selected rows. Capture those through their ordinary procedure-call syntax when needed. Do not expect the return code to appear automatically as another column in the table.

Result metadata inspection can help before execution. Dynamic SQL, temporary objects, and different branches can limit automatic discovery. Verify actual supported branches instead of treating a successful metadata query as a complete interface test.

Recognize the Nested INSERT EXEC Limit

SQL Server rejects nested INSERT EXEC operations in the same execution chain. A wrapper can contain an inner capture even when its caller only sees an ordinary procedure name. Inspect the complete call chain when error 8164 appears. The outer capture below is meant to fail. Its CATCH block returns error 8164, which says an INSERT EXEC statement cannot be nested.

CREATE OR ALTER PROCEDURE dbo.InnerCapture
AS
BEGIN
    SET NOCOUNT ON;
    CREATE TABLE #InnerItems
    (
        ItemId int NOT NULL,
        ItemCode varchar(20) NOT NULL
    );
    INSERT #InnerItems(ItemId, ItemCode)
    EXEC dbo.ResultItems @MinimumId = 1;
    SELECT ItemId, ItemCode FROM #InnerItems;
END;
GO
BEGIN TRY
    INSERT #CapturedItems(ItemId, ItemCode)
    EXEC dbo.InnerCapture;
END TRY
BEGIN CATCH
    SELECT ERROR_NUMBER() AS ErrorNumber,
           ERROR_MESSAGE() AS ErrorMessage;
END CATCH;

The inner capture conflicts with the outer one. Changing temporary-table names does not remove that structural limit. A wrapper procedure adds another call level rather than creating a different execution boundary.

Do not use a loopback connection casually to bypass the error. A separate session changes transaction visibility and can introduce blocking against the original session. Fix the result-sharing design before introducing another connection as a workaround.

Called procedures also need compatible transaction behavior. A rollback executed within this capture chain has restrictions that ordinary standalone execution does not share. Review error cleanup and transaction ownership before using a general-purpose modification procedure as a row source.

Handle Multiple Result Sets Deliberately

Every result set emitted by the execution must be compatible with the destination shape. Compatible sets contribute rows to the same capture. The operation does not provide a selector saying to capture only result set number two.

CREATE OR ALTER PROCEDURE dbo.TwoCompatibleSets
AS
BEGIN
    SET NOCOUNT ON;
    SELECT CONVERT(int, 10) AS ItemId,
           CONVERT(varchar(20), 'First set') AS ItemCode;
    SELECT CONVERT(int, 20) AS ItemId,
           CONVERT(varchar(20), 'Second set') AS ItemCode;
END;
GO
TRUNCATE TABLE #CapturedItems;
INSERT #CapturedItems(ItemId, ItemCode)
EXEC dbo.TwoCompatibleSets;
SELECT ItemId, ItemCode FROM #CapturedItems ORDER BY ItemId;

A later incompatible result set can make the capture fail. Do not assume the earlier compatible rows establish a successful operation. Handle the error and verify the destination according to the surrounding transaction contract.

WITH RESULT SETS cannot be specified as part of INSERT EXEC. It is not a mechanism for reshaping or suppressing unwanted sets during this capture. Refactor the procedure or use an interface designed for the required rows.

Keep a stable result interface for procedures used by several consumers. Adding an informational SELECT affects each capture consumer. Diagnostic output belongs in a separate supported channel when it is not part of the promised rowset.

Prefer a Composable Relational Interface When Possible

An inline table-valued function is a useful alternative for a reusable read-only query. Callers can join, filter, and aggregate its rows directly. It does not support arbitrary procedure side effects or every procedural operation.

CREATE OR ALTER FUNCTION dbo.ResultItemsFunction(@MinimumId int)
RETURNS TABLE
AS
RETURN
(
    SELECT ItemId, ItemCode
    FROM dbo.ResultItem
    WHERE ItemId >= @MinimumId
);
GO
SELECT ItemId, ItemCode
FROM dbo.ResultItemsFunction(2)
WHERE ItemCode = 'A200';

A caller-owned temporary table is another explicit contract when a procedure must perform procedural work. The caller creates the table and the procedure inserts into that known shape. Document its name, lifetime, and ownership rather than hiding the dependency.

An output table parameter is not a supported SQL Server procedure alternative. Table-valued parameters are READONLY inputs and cannot use OUTPUT. For small structured output, an ordinary nvarchar(max) output parameter carrying validated JSON is a different interface.

OPENROWSET can expose a rowset from a separate provider connection when the environment permits it. That requires appropriate provider, access, authentication, and configuration review. It introduces another connection boundary, so it is not a drop-in local replacement.

Do not enable ad hoc distributed access merely to avoid refactoring one capture. Evaluate the transaction, permissions, credentials, and operational requirements of that alternative. The simplest relational interface usually leaves fewer surprises for future callers.

Test the Whole Interface before Depending on It

Does every supported parameter branch return the same intended columns without an inner capture? Test empty results, maximum lengths, NULL values, and each result-producing branch. Include failure cleanup and transaction behavior in the same review.

Add a contract test when a procedure is already consumed through procedure capture. A change in column order or an extra result set can otherwise break a distant caller. Preserve that interface through deliberate schema and deployment review.

Capture into a table only when the procedure's complete behavior fits the technique. A composable function or explicit result-sharing contract is clearer when callers need relational reuse. The selected interface should explain its limits instead of relying on accidental compatibility.

Related reading on this blog: Using Stored Procedure in SELECT: SQL in Sixty Seconds #193 and Dropping Temp Table in Stored Procedure: SQL in Sixty Seconds #124.

A procedure result is not a table interface, it is an ordered contract that the capture must satisfy.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.