sql server Invalid object name '#temp'

Hi , 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 "Invalid object name '#temp' " error message

but if i use ##temp means its 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 the What might be the issue . How can i solve it ?

Please help.

From stackoverflow

• 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?

  Jebli : 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.

• thanks AdaTheDev had given me the right solution.