What's the scoping rule for temporary tables within exec within stored procedures?

Question

Compare the following stored procedures:

CREATE PROCEDURE testProc1
AS
    SELECT * INTO #temp FROM information_schema.tables
    SELECT * FROM #temp
GO

CREATE PROCEDURE testProc2
AS
    EXEC('SELECT * INTO #temp FROM information_schema.tables')
    SELECT * FROM #temp
GO

Now, if I run testProc1, it works, and #temp seems to only exist for the duration of that call. However, testProc2 doesn't seem to work at all, since I get an Invalid object name '#temp' error message instead.

Why the distinction, and how can I use a temp table to SELECT * INTO if the source table name is a parameter to the stored procedure and can have arbitrary structure?

Note that I'm using Microsoft SQL Server 2005.

Answer 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:

CREATE PROCEDURE [dbo].[testProc3]
AS
    SELECT * 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