SQL Server shows "Invalid object name '#temp'" when working with a temporary table

Question

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:

Invalid object name '#temp'

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

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

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

fedorqui · Accepted Answer · 2017-06-30 13:30:12Z

26

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

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.

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

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.

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

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?

edited Jun 30, 2017 at 13:30

fedorqui

294k113 gold badges593 silver badges640 bronze badges

answered Aug 12, 2009 at 11:19

AdaTheDev

148k28 gold badges215 silver badges204 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jebli Over a year ago

Thanks.I know it was out of scope but it didn't strike for me that i have to do the select with the same query.

hjpotter92 · Accepted Answer · 2016-09-09 05:49:17Z

6

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

This rectified the problem for me.

edited Sep 9, 2016 at 5:49

hjpotter92

81.1k36 gold badges148 silver badges188 bronze badges

answered Sep 9, 2016 at 5:29

Bhoopathi Reddy

891 silver badge8 bronze badges

2 Comments

Gary Apr 29 at 14:15

Ok, this worked, but WHY?

Gary May 2 at 2:54

Never mind. I found a GO in my sql file, the #tempTable (as well as a declared @var) was no longer available after the GO (as expected, if I had noticed the GO earlier)

fedorqui · Accepted Answer · 2017-06-30 13:22:06Z

1

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

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.

edited Jun 30, 2017 at 13:22

fedorqui

294k113 gold badges593 silver badges640 bronze badges

answered Jun 9, 2011 at 16:06

mahesh

111 bronze badge

Collectives™ on Stack Overflow

SQL Server shows "Invalid object name '#temp'" when working with a temporary table

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related