Discussion stickers: The visibility of temporary tables generated in sp_executesql

Create a data table first

IF object_id('testtable') is  not NULL    DROP TABLEtesttableGOCREATE TABLETestTable (IDINT IDENTITY(1,1), InfoVARCHAR(Ten))GOINSERTTestTableSELECT 'a'UNION  All SELECT 'b'GO

Then execute the following three scripts in turn

Script one:

EXEC (' SELECT * to #temp from TestTable ')
SELECT * from #temp

Script two:

exec sp_executesql N ' SELECT * to #temp from TestTable '
SELECT * from #temp

Script Three:

SELECT * to #temp from TestTable
EXEC (' SELECT * from #temp ')

--drop TABLE #temp

After execution, it is found that only the third script is successful, and other scripts will prompt after execution:

MSG 208, level A, state 0, line 2
Invalid object name ' #temp '.

Their different expenditures are the order in which SQL statements are executed,

My understanding is:

Changes made to the database context are only valid until the exec sp_executesql, or exec (' ... ') statement ends.

If the temporary table is generated before sp_executesql, then it is willing to receive, if the sp_executesql is executed first to create the temporary table,

When the statement is finished, the temporary table disappears.

Expect to hear more authoritative answers ...

Discussion stickers: The visibility of temporary tables generated in sp_executesql