Understand temporary-table scope across dynamic SQL

A procedure builds a temporary table with dynamic SQL, then tries to query it in the next statement. The insert succeeded, but the query reports an invalid object name. The usual cause is scope: a local temporary table can be visible to nested work while a table created inside that nested scope does not survive its completion.

Put ownership in the longer-lived scope

The following example creates #OuterWork in the calling batch. Dynamic SQL inserts into it and creates its own #InnerWork. After the dynamic batch ends, the caller can still read #OuterWork. A separate dynamic query against #InnerWork raises error 208, captured by the outer TRY/CATCH.

CREATE TABLE #OuterWork (ItemId int NOT NULL PRIMARY KEY);

EXEC sys.sp_executesql N'
    INSERT #OuterWork (ItemId) VALUES (@Id);
    CREATE TABLE #InnerWork (ItemId int NOT NULL);
    INSERT #InnerWork VALUES (99);
    SELECT ItemId AS VisibleInside FROM #InnerWork;',
    N'@Id int', @Id = 7;

SELECT ItemId AS VisibleOutside FROM #OuterWork;

BEGIN TRY
    EXEC sys.sp_executesql N'SELECT ItemId FROM #InnerWork;';
END TRY
BEGIN CATCH
    SELECT ERROR_NUMBER() AS ErrorNumber,
           ERROR_MESSAGE() AS ErrorMessage;
END CATCH;

DROP TABLE #OuterWork;

The first result contains 99, the second contains 7, and the final result describes the missing inner object. No second connection is involved. This is a scope boundary within one session, which is why simply keeping the connection open does not preserve the inner table.

When later statements need the data, create the temporary table with an explicit schema in the outer scope and let the dynamic batch populate it. That ownership is easy to review: the caller defines the contract, nested work fills it, and the caller consumes and drops it.

The same reasoning applies to stored procedures. A local temporary table created by a procedure is removed when that procedure finishes, although nested procedures can use it while it exists. A procedure can use a caller-created temporary table, but this introduces an implicit dependency. Document expected columns and constraints, or choose an explicit parameter contract when that better fits the interface.

Keep values, schemas, and connections separate

sp_executesql executes a separate batch. Ordinary scalar variables from the caller are not automatically available there; pass values as typed parameters, as @Id demonstrates. This differs from the visibility of an already-existing local temporary table. Confusing the two rules often leads to unnecessary string concatenation.

A table variable also does not become accessible inside dynamic SQL merely because its declaration appears nearby. If the operation naturally accepts a set, a table-valued parameter with a defined type can provide an explicit input contract. For mutable intermediate results shared with nested dynamic work, an outer temporary table can be the simpler design.

Dynamic column names are a different problem from dynamic values. A table whose output schema changes for every request is hard for later static statements and clients to consume. Consider returning the dynamic result directly, or representing varying attributes as rows with stable columns. Do not turn user-supplied identifiers into executable text without validation and appropriate identifier quoting.

A local temporary table belongs to a physical SQL session. Two application calls are not guaranteed to receive the same pooled connection. Keeping a table for a later web request is therefore an unreliable state-management strategy, even when it appears to work during a single-user test.

Avoid fixes that create hidden sharing

Changing #Work to ##Work creates a global temporary table, which changes visibility and lifetime rules. It does not merely extend a local variable's life. Concurrent requests can collide over the name or observe each other's data. For cross-request work, a persistent staging table keyed by a unique job identifier usually provides a clearer isolation and cleanup contract.

Avoid reusing the same temporary-table name in nested scopes. SQL Server can have same-named local temporary objects in nested execution contexts, making resolution and maintenance surprising. Give separate responsibilities separate names rather than depending on which object a statement happens to resolve.

Do not interpret temporary storage as unlimited storage. Wide rows, indexes, and long-lived sessions can keep tempdb space occupied. Drop large intermediate tables when their useful lifetime ends, especially in a long procedure that continues with unrelated work. Also consider transaction rollback behavior when reasoning about whether inserted data should remain.

Test the contract through the real application path: nested procedure calls, dynamic batches, errors, and separate pooled connections. The useful fix is an explicit owner and lifetime for the data. Once that boundary is clear, invalid-object errors usually become predictable rather than intermittent.

Technical references: Microsoft Learn: CREATE TABLE and temporary scope · Microsoft Learn: sp_executesql.