动态SQL创建临时表成功却无法查询的问题求助

为什么查不到临时表？ #

本地临时表（以#开头）的作用域仅限于创建它的批处理、存储过程或执行动态SQL的sp_executesql会话。当sp_executesql执行完毕后，这个临时表会被自动销毁，所以外部的SELECT语句找不到它。

解决方案 #

方案1：改用全局临时表（##前缀） #

全局临时表对所有会话可见，只有当创建它的会话关闭且没有其他会话引用时才会被销毁。示例代码：

IF OBJECT_ID('tempdb..##blankOuputLevel2') IS NOT NULL
BEGIN
    DROP TABLE ##blankOuputLevel2
END

DECLARE @sql NVARCHAR(MAX)
SET @sql = 'CREATE TABLE ##blankOuputLevel2 (tjob NVARCHAR(30));'
EXEC sp_executesql @sql

-- 此时可以正常查询
SELECT * FROM ##blankOuputLevel2

注意：全局临时表容易出现命名冲突，若多个会话同时创建同名全局临时表会报错。

方案2：将查询语句放入动态SQL中一起执行 #

让创建表和查询表在同一个sp_executesql批处理里，这样临时表作用域覆盖整个批处理：

IF OBJECT_ID('tempdb..#blankOuputLevel2') IS NOT NULL
BEGIN
    DROP TABLE #blankOuputLevel2
END

DECLARE @sql NVARCHAR(MAX)
SET @sql = 'CREATE TABLE #blankOuputLevel2 (tjob NVARCHAR(30));
SELECT * FROM #blankOuputLevel2;'
EXEC sp_executesql @sql

方案3：先在外部创建临时表，动态SQL中仅插入数据 #

先在主会话创建临时表，再用动态SQL插入数据，这样临时表的作用域属于主会话，后续可随时查询：

-- 主会话中创建临时表
IF OBJECT_ID('tempdb..#blankOuputLevel2') IS NOT NULL
BEGIN
    DROP TABLE #blankOuputLevel2
END
CREATE TABLE #blankOuputLevel2 (tjob NVARCHAR(30));

-- 动态SQL中插入数据
DECLARE @sql NVARCHAR(MAX)
SET @sql = 'INSERT INTO #blankOuputLevel2 VALUES (''测试任务'');'
EXEC sp_executesql @sql

-- 主会话中正常查询
SELECT * FROM #blankOuputLevel2

这是最常用的方案，适合需要多次操作临时表的场景。

内容的提问来源于stack exchange，提问作者TrungT