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:
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.
Best Answer
From an answer by Remus Rusanu:
You can validate that you are using the same session in the
sp_executesql
call by running:Knowing that
sp_executesql
runs under a different scope but not a different session, adding information found in the docs on temporary tables:And that same source on the visibility of temporary tables:
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.