Select From EXEC Stored Procedure Results in SQL Server

SQL Server does not let you write SELECT col FROM (EXEC proc). To select specific columns from stored procedure results, capture the full rowset into a temporary table with INSERT EXEC, or read it with OPENROWSET and then choose columns, or use WITH RESULT SETS to rename—not filter—columns. The examples in this note were tested on SQL Server 2019 CU18.

Why you cannot put a SELECT around EXEC

EXEC returns a rowset to the client, not a table that a SELECT statement can consume. SQL Server has no syntax for SELECT * FROM (EXEC dbo.usp_Report), so the rowset must first land in a target with a defined shape. The two practical destinations are a table target through INSERT EXEC and an OLE DB provider through OPENROWSET. Both approaches capture the full result set before you can project only the columns you need, because the stored procedure output has no relational identity until it is stored or passed through a provider that implements a FROM clause.

Method 1 — INSERT EXEC into a temporary table

The workhorse for local capture is a temporary table. Create #out with the same column order and compatible data types as the procedure output, then run INSERT #out EXEC dbo.usp_Report;. After that, you can query the temp table and pick columns exactly as you would from a normal table. A local temporary table is the usual target; a table variable can also receive the rowset within its scope, but you cannot use it across batches.

CREATE TABLE #out
(
    OrderID      int           NOT NULL,
    CustomerName nvarchar(100) NOT NULL,
    OrderDate    date          NULL,
    TotalDue     decimal(18,2) NULL
);

INSERT #out EXEC dbo.usp_OrderSummary @year = 2024;

SELECT OrderID, TotalDue
FROM #out
WHERE TotalDue > 500.00;

The column list on the CREATE TABLE statement must match the shape of the first result set returned by the procedure. Column names do not need to match; SQL Server inserts by ordinal position, and the receiving data types must permit a safe implicit conversion. Because INSERT EXEC puts every output column into that target, this method does not avoid storing columns you do not need; it lets you ignore them in the later SELECT. The INSERT (Transact-SQL) syntax uses the execute_statement form of the INSERT statement, and each row returned by the procedure becomes one row in the target table. INSERT EXEC runs on the local instance without linked servers, but the target must exist before execution.

Method 2 — Select chosen columns with OPENROWSET

OPENROWSET opens an ad hoc OLE DB connection, executes the procedure, and exposes the first result set to a FROM clause. That means you can wrap it in a derived table and select only the columns you want from the returned rowset. OPENROWSET belongs to the same ad hoc access family as OPENDATASOURCE, but OPENROWSET is the member that can sit directly in a FROM clause.

SELECT r.OrderID, r.OrderDate
FROM OPENROWSET(
    'MSOLEDBSQL',
    'Server=(local);Trusted_Connection=yes;',
    'EXEC dbo.usp_OrderSummary @year = 2024;'
) AS r;

The string after MSOLEDBSQL is a provider-specific connection string. For a local loopback connection you still need the OLE DB provider and a login with permission to execute the procedure. The OPENROWSET (Transact-SQL) page describes this function as a one-time, ad hoc method of connecting and accessing remote data; for frequent references it recommends linked servers. Although the query may return multiple result sets, OPENROWSET returns only the first one.

Because OPENROWSET sits in a FROM clause, it supports joins directly, so you do not need to stage the rowset first if you only want to shape it and combine it with other tables.

SELECT o.OrderID, c.CustomerRegion
FROM OPENROWSET(
    'MSOLEDBSQL',
    'Server=(local);Trusted_Connection=yes;',
    'EXEC dbo.usp_OrderSummary @year = 2024;'
) AS o
JOIN dbo.Customers AS c
    ON c.CustomerID = o.CustomerID;

The OPENROWSET (Transact-SQL) page notes that the SQL Server Native Client providers (SQLNCLI or SQLNCLI11) have been removed from SQL Server 2022 (16.x) and SQL Server Management Studio 19 (SSMS), and Microsoft no longer recommends them for new development. Switch to the new Microsoft OLE DB Driver for SQL Server (MSOLEDBSQL) for new development.

Enabling ad hoc distributed queries opens a server-side path that can weaken isolation. Make the change only under a DBA-reviewed policy, and prefer linked servers for repeated access.

Method 3 — Check columns first with sys.dm_exec_describe_first_result_set

Before you write a destination table, you can query the metadata of the first result set without running the procedure. The DMV sys.dm_exec_describe_first_result_set takes a T-SQL batch, an optional parameter declaration string, and a browse-information flag, and returns one row per output column.

SELECT name, system_type_name, is_nullable
FROM sys.dm_exec_describe_first_result_set(
    N'EXEC dbo.usp_OrderSummary @year = 2024;',
    NULL,
    0
);

A returned row looks like this for a typical column:

The system_type_name column gives the type name together with length, precision, or scale, which is exactly the information you need to write a matching CREATE TABLE. Alongside name and system_type_name, the function exposes column_ordinal, is_nullable, max_length, precision, and scale. Use column_ordinal to match positions when the destination table uses different names. The @params argument is required only when the batch contains embedded parameter placeholders; pass NULL when the statement uses literal values. The function returns the same result set definition as sys.dm_exec_describe_first_result_set_for_object and is similar to sp_describe_first_result_set. It is not available before SQL Server 2012.

Choice table — INSERT EXEC vs OPENROWSET vs WITH RESULT SETS

WITH RESULT SETS is useful when a procedure returns anonymous or poorly named columns, but it should not be treated as a column filter. If you need fewer columns, use INSERT EXEC with a later select or OPENROWSET in a derived table.

Errors and fallbacks

SQL Server raises the following error when one INSERT EXEC tries to run inside another:

An INSERT EXEC statement cannot be nested.

The usual fix is to insert the first result into a staging table and then let the outer procedure use that table, or replace the inner capture with OPENROWSET.

Another common failure is The metadata could not be determined because statement..., which can appear when a procedure uses dynamic SQL or temporary objects that the metadata function cannot resolve. In that case, inspect the procedure output with a test execution or use a destination table whose definition you already trust.

Frequently asked questions

Can you SELECT from the result of a stored procedure in SQL Server?

Not directly. Use INSERT EXEC to capture the rowset into a temporary table, use OPENROWSET to expose the first result set to a FROM clause, or use WITH RESULT SETS to rename output columns without filtering them.

How do I capture stored procedure output into a temp table?

Create the #table with matching column order and types, then run INSERT #table EXEC dbo.proc;. The procedure must return a rowset that fits the destination shape.

Why does INSERT EXEC fail inside another INSERT EXEC?

SQL Server forbids nested INSERT EXEC statements, so the inner call cannot insert directly into a target already consumed by an outer INSERT EXEC. Use OPENROWSET or a staging table as a fallback.

How do I get only certain columns from procedure results?

Capture the full rowset and select the columns you need from the captured table, or use OPENROWSET inside a derived table and choose columns in the outer query.

What is sys.dm_exec_describe_first_result_set used for?

It returns column names, data types, nullability and other metadata for the first result set of a batch, so you can plan a destination table without running the procedure.

Sources

• Microsoft Learn, INSERT (Transact-SQL).
• Microsoft Learn, OPENROWSET (Transact-SQL).
• Microsoft Learn, sys.dm_exec_describe_first_result_set (Transact-SQL).