CODEWITHSUNDEEP
sql-serversql-server-2005temp-tables
Jebli
Jebli

Reputation: 2825

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

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?

Upvotes: 24

Views: 82573

Answers (3)

AdaTheDev
AdaTheDev

Reputation: 147224

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:

  1. Do you really need to use temp tables, can you not find a solution without them using e.g. a subquery?
  2. Do you really need to execute sql like this using sp_executesql?

Upvotes: 26

mahesh
mahesh

Reputation: 11

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.

Upvotes: 1

Bhoopathi Reddy
Bhoopathi Reddy

Reputation: 89

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.

Upvotes: 6

Related Questions