How to create a Local Temp Table using command sp_executesql
Created a stored procedure with the following statments
declare @SqlQuery
@SqlQuery = 'select * into #test from dept'
execute sp_executesql @SqlQuery
select * from #test
If I execute the Stored procedure. The following error is produced
server: Msg 208, Level 16, State 1, Procedure Test, Line 15
Invalid object name '#test'
declare @SqlQuery
@SqlQuery = 'select * into #test from dept'
execute sp_executesql @SqlQuery
select * from #test
If I execute the Stored procedure. The following error is produced
server: Msg 208, Level 16, State 1, Procedure Test, Line 15
Invalid object name '#test'
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
Guy Hengel [angelIII / a3]
you cannot. once the sql_executesql is finished, the temp table from inside the dynamic sql has gone out of scope, and the temp table is gone.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
What about a global temp table, so it stays in scope until connection is closed?
As Makolyte suggests, a global temp table should solve your problem. You use it like this:
declare @SqlQuery nvarchar(1000)
set @SqlQuery = 'select * into ##test from dept'
execute sp_executesql @SqlQuery
select * from ##test
Please note that I made a couple of syntax corrections to your statements.
declare @SqlQuery nvarchar(1000)
set @SqlQuery = 'select * into ##test from dept'
execute sp_executesql @SqlQuery
select * from ##test
Please note that I made a couple of syntax corrections to your statements.
ASKER
Thanks. it is working fine