当使用临时表时，SQL Server显示“无效的对象名称'#temp'”

Question

我创建了一个过程

create procedure testProcedure_One 
as
DECLARE @Query nvarchar(4000)

begin
SET @Query = 'SELECT * into #temptest FROM  Table1'

Exec sp_Executesql @query

SELECT * FROM #temptest
drop table #temptest
end

当我运行testProcedure_One过程时，我收到以下错误信息：

Invalid object name '#temp'

但是如果我使用 ##temp means 就可以正常工作：

create procedure testProcedure_two 
as
DECLARE @Query nvarchar(4000)

begin

SET @Query = 'SELECT * into ##temptest FROM  Table1'


Exec sp_Executesql @query

SELECT * FROM ##temptest
drop table ##temptest
end

testProcedure_two 工作正常。

可能的问题是什么？我该如何解决？

- Jebli

Answer 1

假设您有以下代码，从#temp中进行选择，导致了错误？

这是作用域问题。##temp是全局临时表，在其他会话中可用。#temp是“本地”临时表，仅由当前执行范围访问。sp_executesql在不同的作用域下运行，因此它将数据插入#temp，但如果您尝试在sp_executesql调用之外访问该表，则无法找到它。

例如， 这会导致错误，因为#Test被创建并且仅在sp_executesql上下文中可见：

EXECUTE sp_executesql N'SELECT 1 AS Field1 INTO #Test'
SELECT * FROM #Test

上面的代码可以与##Test一起使用，因为它创建了一个全局临时表。

这段代码有效是因为SELECT语句属于同一作用域。

EXECUTE sp_executesql N'SELECT 1 AS Field1 INTO #Test; SELECT * FROM #Test'

我想问的是：

1. 你是否真的需要使用临时表？是否可以使用子查询等方法在不使用临时表的情况下找到解决方案？
2. 你是否真的需要使用sp_executesql这样的方式执行SQL语句？

- AdaTheDev

---

谢谢。我知道这超出了范围，但我没有想到我必须使用相同的查询来执行选择操作。 - Jebli

Answer 2

使用CREATE TABLE创建临时表，然后使用INSERT INTO插入值，而不是使用SELECT INTO语句。这样可以解决我的问题。

- Bhoopathi Reddy

Answer 3

我认为你需要使用DDL语句先创建#tmp_table，然后再执行你的exec和存储过程。在exec中创建的临时表应该与之前创建的同名，即#tmp_table。

- mahesh