sql server Invalid object name '#temp'

Question

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.

Answer 1

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?

Answer 2

thanks AdaTheDev had given me the right solution.