sql-server - 在存储过程中引用时，动态命名的临时表返回 "invalid object name"

Question

当我运行以下代码时，出现“无效的对象名称”错误，知道为什么吗？

我需要创建一个动态命名的临时表以在存储过程中使用。

 DECLARE @SQL NVARCHAR(MAX)
 DECLARE @SessionID NVARCHAR(50)
 SET @SessionID = 'tmp5l7g9q3l1h1n5s4k9k7e'
 ;
 SET        
 @SQL = N'      CREATE TABLE #' + @SessionID + ' ' +
         N'     (' +
    N'      CustomerNo NVARCHAR(5), ' +
    N'      Product NVARCHAR(3), ' + 
    N'      Gross DECIMAL(18,8) ' +
    N'      )'
 ;
 EXECUTE sp_executesql @SQL
 ;
 SET        
 @SQL = N'      SELECT * FROM #' + @SessionID
 ;
 EXECUTE sp_executesql @SQL

谢谢!

Answer 1

为什么要搞乱这些名称？让 SQL Server 为您管理这个:

Temporary Tables in SQL Server

来自上面的链接:

If the same routine is executed simultaneously by several processes, the Database Engine needs to be able to distinguish between the identically-named local temporary tables created by the different processes. It does this by adding a numeric string to each local temporary table name left-padded by underscore characters. Although you specify the short name such as #MyTempTable, what is actually stored in TempDB is made up of the table name specified in the CREATE TABLE statement and the suffix. Because of this suffix, local temporary table names must be 116 characters or less.

If you’re interested in seeing what is going on, you can view the tables in TempDB just the same way you would any other table. You can even use sp_help work on temporary tables only if you invoke them from TempDB.

USE TempDB
go
execute sp_Help #mytemp 

or you can find them in the system views of TempDB without swithching databases.

SELECT name, create_date FROM TempDB.sys.tables WHERE name LIKE '#%'