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?

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?