RE: Temporary table with dynamic sql

SSC Guru

Points: 186127

October 15, 2008 at 2:59 am

That's interesting, I also assumed it took a new connection.

If you create the temp table before executing the string, then (as Barry's code shows) the table is visible to the session taken by the executing string. But if the executing string creates the temp table, then the temp table is not visible to the original session:

[font="Courier New"]SET NOCOUNT ON

DECLARE @Sql NVARCHAR(100)

SET @Sql = 'SELECT TOP 1 [collation] INTO #temp FROM master.dbo.syscolumns'

EXEC(@Sql)

EXEC sp_executesql @Sql

SELECT * FROM #temp

"Server: Msg 208, Level 16, State 1, Line 9

Invalid object name '#temp'."

[/font]

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden