3

If local temp tables are only available to the current session, and sp_ExecuteSql creates a new session to execute the dynamic SQL string passed into it, how can that dynamic SQL query access a temp table created in the session that executes sp_ExecuteSql.

In other words, why does this work:

SELECT 1 AS TestColumn
INTO #TestTempTable

DECLARE @DS NVARCHAR(MAX) = 'SELECT * FROM #TestTempTable'
EXEC sp_EXECUTESQL @DS

Results:

Temp Table Results from Dynamic SQL Select

My understanding for the reason why I can't do the opposite (create the temp table in Dynamic SQL and then access it outside the dynamic SQL query in the executing session) is because sp_ExecuteSql executes under a new session.

8

From an answer by Remus Rusanu:

Dynamic SQL runs in the same session as the calling code. The issue is not session, but scope. Your dynamic SQL creates the temporary tables inside the sp_executesql call and thus the created #temp table is only visible within that sp_executesql call, as documented in MSDN:

You can validate that you are using the same session in the sp_executesql call by running:

DECLARE @DS NVARCHAR(MAX) = 'SELECT @@SPID'
EXEC sp_EXECUTESQL @DS

Knowing that sp_executesql runs under a different scope but not a different session, adding information found in the docs on temporary tables:

Temporary tables are automatically dropped when they go out of scope, unless explicitly dropped by using DROP TABLE:

And that same source on the visibility of temporary tables:

Local temporary tables are visible only in the current session

These differences in scope vs. session explain why you are able to access the temporary table from the sp_executesql call but not the other way around.

  • Great answer, thank you! – J.D. Dec 10 '19 at 14:33
  • 1
    @J.D. Also a great question :). – Randi Vertongen Dec 10 '19 at 14:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.