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

I have created a procedure

我创建了一个过程

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

When I run the procedure testProcedure_One I am getting the error message:

当我运行过程testprocedure testprocedure testprocessdure_1时，我得到了错误消息:

Invalid object name '#temp'

But if I use ##temp means it's working:

但是如果我使用##temp表示它正在工作:

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 is working fine

testProcedure_two工作正常

What might be the issue? How can i solve it?

问题可能是什么?我怎么解决呢?

3 个解决方案

#1

Presumably you have following code that SELECTs from #temp, giving you the error?

假设您有以下代码从#temp中选择，并给出错误?

It's down to scope. ##temp is a global temporary table, available in other sessions. #temp is "local" temporary table, only accessible by the current executing scope. sp_executesql runs under a different scope, and so it will insert the data into #temp, but if you then try to access that table outside of the sp_executesql call, it won't find it.

它的范围。##temp是一个全局临时表，可以在其他会话中使用。#temp是“本地”临时表，只能由当前执行范围访问。sp_executesql在不同的范围下运行，因此它将把数据插入到#temp中，但是如果您尝试访问sp_executesql调用之外的表，它将找不到它。

e.g. This errors as #Test is created and only visible to, the sp_executesql context:

例:这个错误作为#Test被创建，并且只对sp_executesql上下文可见:

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

The above works with ##Test as it creates a global temporary table.

上面的方法可以在创建全局临时表时使用##Test。

This works, as the SELECT is part of the same scope.

这是可行的，因为SELECT是相同范围的一部分。

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

My questions would be:

我的问题是:

Do you really need to use temp tables, can you not find a solution without them using e.g. a subquery?
您真的需要使用临时表吗?如果没有临时表，您是否可以使用子查询来找到解决方案?
Do you really need to execute sql like this using sp_executesql?
您真的需要使用sp_executesql执行这样的sql吗?

#2

for you to execute i think that the #tmp_table should be created first using a ddl statement.

对于执行，我认为应该首先使用ddl语句创建#tmp_table。

Then you execute your exec and the stored proc you have created in the exec should have the same named temp table viz.#tmp_table.

然后执行您的exec和在exec中创建的存储proc应该具有相同的命名为temp表，即#tmp_table。

#3

Create the Temporary table by using CREATE TABLE and then use INSERT INTO to insert the values instead of SELECT INTO.

通过使用Create table创建临时表，然后使用INSERT INTO来插入值，而不是SELECT INTO。

This rectified the problem for me.

这纠正了我的问题。

#1