What's The Scoping Rule For Temporary Tables Within Exec Within Stored Procedures?

Compare the following stored procedures: CREATE PROCEDURE testProc1 AS SELECT * INTO #temp FROM information_schema.tables SELECT * FROM #temp GO CREATE PROCEDURE testProc2

Solution 1:

From BOL:

Local temporary tables are visible only in the current session... ... Temporary tables are automatically dropped when they go out of scope, unless explicitly dropped using DROP TABLE

The distinction between your first and second procedures is that in the first, the table is defined in the same scope that it is selected from; in the second, the EXEC() creates the table in its own scope, so the select fails in this case...

However, note that the following works just fine:

CREATEPROCEDURE [dbo].[testProc3]
ASSELECT*INTO #temp FROM information_schema.tables
    EXEC('SELECT * FROM #temp')
GO

And it works because the scope of EXEC is a child of the scope of the stored procedure. When the table is created in the parent scope, it also exists for any of the children.

To give you a good solution, we'd need to know more about the problem that you're trying to solve... but, if you simply need to select from the created table, performing the select in the child scope works just fine:

CREATE PROCEDURE [dbo].[testProc4]
AS
    EXEC('SELECT * INTO #temp FROM information_schema.tables; SELECT * FROM #temp')
GO

Solution 2:

You could try using a global temp table (named ##temp not #temp). However be aware that other connections can see this table as well.