24

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
  • 275,237
  • 103
  • 548
  • 598
Jebli
  • 2,775
  • 6
  • 29
  • 37

3 Answers3

25

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?
fedorqui
  • 275,237
  • 103
  • 548
  • 598
AdaTheDev
  • 142,592
  • 28
  • 206
  • 200
  • 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. – Jebli Aug 12 '09 at 11:25
5

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.

hjpotter92
  • 78,589
  • 36
  • 144
  • 183
Bhoopathi Reddy
  • 79
  • 1
  • 8
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.

fedorqui
  • 275,237
  • 103
  • 548
  • 598
mahesh
  • 11
  • 1