What is the rule for defining temporary tables inside exec in 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 exist only for the duration of this call. However, testProc2 does not seem to work at all, because instead I get the error Invalid object name '#temp' .

Why is there a difference and how can I use a temporary table for SELECT * INTO if the name of the source table is a parameter of the stored procedure and can have an arbitrary structure?

Please note that I am using Microsoft SQL Server 2005.

Answer 1

From BOL:

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

The difference between your first and second procedures is that in the first case, the table is defined in the same area in which it is selected; in the second, EXEC () creates a table in its own scope, so the selection is not suitable 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 this works because the EXEC scope is a child of the stored procedure scope. When a table is created in the parent area, it also exists for any of the children.

To give you a good solution, we will need to find out more about the problem you are trying to solve ... but if you just need to select from the created table, making a selection in the area of ​​child objects works fine:

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

Answer 2

You can try using the global temp table (named ## temp not #temp). However, keep in mind that other connections may also see this table.