Invalid object name '#temp' and error 2714 about an existing #temp can both appear in a stored procedure workflow. Temp table scope determines which batch can see the table and when it disappears. Once the creation scope is clear, nested procedures and dynamic SQL become predictable.
Start Temp Table Scope With the Owning Session
A local temporary table belongs to a session and has a scope tied to the batch or procedure that created it. A table created in an outer procedure is visible to procedures it calls. A table created in a nested procedure is normally dropped when that procedure ends. It is not a dependable return value to the caller. A dynamic batch run through sp_executesql is another nested scope.
I draw the call stack before changing SQL. Which scope ran CREATE TABLE, and which scope later ran SELECT? A temporary name in SSMS Object Explorer does not answer that; the sequence of calls does.
Let an Inner Procedure Read an Outer Table
The caller creates #Work and then executes a procedure that reads it. The procedure can be created before the temp table exists; SQL Server resolves the table at execution. This pattern is useful when a trusted caller owns the table contract, but the procedure must document required columns clearly.
CREATE OR ALTER PROCEDURE dbo.ReadWorkTemp
AS
BEGIN
SET NOCOUNT ON;
SELECT WorkID, WorkValue FROM #Work ORDER BY WorkID;
END;
GO
CREATE TABLE #Work
(
WorkID int NOT NULL PRIMARY KEY,
WorkValue varchar(30) NOT NULL
);
INSERT #Work VALUES (1,'ready'),(2,'checked');
EXEC dbo.ReadWorkTemp;
DROP TABLE #Work;The call returns two rows. Another caller that runs the procedure without creating #Work gets an invalid-object error. Treat the temp table definition as an interface, not a hidden implementation detail. If several callers need it, a table-valued parameter can be a clearer contract.
See a Dynamic-SQL Table Disappear
A table created inside sp_executesql exists within that dynamic batch, but is gone when the call returns. Selecting it in the outer batch then raises Invalid object name. The same rule explains why a dynamic query can insert into an outer-created temp table: visibility flows inward.
EXEC sys.sp_executesql N'
CREATE TABLE #Inside (ID int NOT NULL);
INSERT #Inside VALUES (1);
SELECT ID FROM #Inside;';
-- This next line fails after the dynamic scope ends:
SELECT ID FROM #Inside;Run the failing line separately when teaching the example. A safe version creates #Inside in the outer scope, then uses dynamic SQL only to populate it. The outer batch owns its lifetime and can read it afterward.
CREATE TABLE #Result (ID int NOT NULL);
EXEC sys.sp_executesql N'INSERT #Result(ID) VALUES (1),(2);';
SELECT ID FROM #Result ORDER BY ID;
DROP TABLE #Result;See How Temp Table Scope Causes Name Clashes
Two CREATE TABLE statements for the same local temp name in one scope raise error 2714. Nested scopes can also create a temp table with the same short name as an outer scope, a behavior sometimes called eclipsing. SQL Server's internal suffixes let both physical tempdb objects exist. Name resolution then becomes hard to reason about when columns or DML overlap. Do not build a workflow that relies on which one a nested statement binds to.
CREATE TABLE #Duplicate (ID int);
GO
-- This second CREATE in the same session raises error 2714:
CREATE TABLE #Duplicate (ID int);
GO
DROP TABLE #Duplicate;The block is meant to fail at the second CREATE. The GO lets the first CREATE run on its own; with both in one batch, SQL Server rejects the pair at compile time and neither runs. A nested procedure with its own #Duplicate is a different scenario and can obscure the outer table rather than throwing this exact error. Use distinct names or one owner for a shared table. Error messages identify the symptom; the scope map identifies the cause.
Pass Rows Between Procedures Safely
For a single call chain, create the temp table in the outer procedure and call inner procedures that insert or update it. Then read it before the outer procedure returns. Give the table one owner and a documented schema. For an API crossing separate sessions, use a table-valued parameter or a keyed staging table; a local temp table is not a cross-session message bus.
CREATE OR ALTER PROCEDURE dbo.FillWorkTemp
AS
BEGIN
SET NOCOUNT ON;
INSERT #Work (WorkID, WorkValue)
VALUES (3, 'from inner procedure');
END;
GO
CREATE TABLE #Work
(
WorkID int NOT NULL PRIMARY KEY,
WorkValue varchar(30) NOT NULL
);
EXEC dbo.FillWorkTemp;
SELECT * FROM #Work;
DROP TABLE #Work;The caller remains responsible for creation, cleanup, and transaction boundaries. If the inner procedure fails, the caller can decide whether to roll back and retry. I keep names specific to the workflow, because a generic #temp invites collisions and makes plan review harder.
Test Temp Table Scope on the Real Call Stack
A procedure can call dynamic SQL that calls another procedure, and each level has its own lifetime. Run the complete call stack on a test database, including error and retry paths. Check whether a temp table is created twice after a catch block, and whether a procedure recompiles because the table schema differs between callers.
I write a small scope table in the runbook: creator, reader, and drop point. That is easier to review than relying on the tempdb physical suffix. A missing table means it was never visible at the read point or had already gone out of scope. A duplicate name means the creation path must be clarified.
Decide Between Temp Tables and Parameters
A table-valued parameter has an explicit type and makes a procedure interface visible. It is read-only inside the callee, though, and has different cardinality behavior. An outer-created temp table allows several nested procedures to modify the same working rows. That interface is implicit and tied to one session. Choose based on who owns the rows and whether the callee needs to change them. For large working sets, compare plans with representative row counts. A neat signature can still need a temp-table staging step for accurate estimates.
Watch Recompilation and Schema Drift
A nested procedure that references #Work assumes a particular column list and data type. If one caller creates WorkID int and another creates WorkID bigint, plans and errors can vary. Keep a shared definition in the caller contract and test all callers after a schema change. SELECT * makes the dependency harder to see, so name the columns the inner procedure requires.
I include cleanup in both success and error paths. A temp table created in an outer batch remains until dropped or the session ends; connection pooling can keep sessions alive across application calls. Explicit DROP at the end makes the intended lifecycle clear, even though SQL Server eventually cleans the object.
Avoid a Misleading Fix
Changing a local #Work to a global ##Work can make an invalid-object error disappear by exposing the table to other sessions. It also creates concurrency, collision, and data-leak risks. A permanent staging table without a run key has similar problems. Fix the ownership and scope first. If cross-session exchange is truly required, use a keyed persistent table with permissions, cleanup, and transaction rules.
Related reading on this blog: Dynamic SQL and Temporary Tables and Dropping Temp Table in Stored Procedure: SQL in Sixty Seconds #124.
A temp table is not global because inner code sees it, it is scoped by the session and creator.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
