sp_executesql and Temp Tables - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

sp_executesql and Temp Tables

Author

Topic

devonkyle
Starting Member

19 Posts

Posted - 2003-10-29 : 14:56:57

I'm trying to create a #temp table via the execution of a
sp_executesql statement but everytime I do a SELECT from the #temp table, I get an error telling me that the #temp table is an invalid object - How can I get the results set of the select into a #temp table via sp_executesql?

Use Northwind
Declare @sqlString nvarchar(1000)
set @sqlstring = N'select * into #tempdatabase from Employees '
print @sqlstring
EXEC sp_executesql @sqlString

select * from #tempdatabase

Thanks
Devon

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2003-10-29 : 15:01:06

try ##tempdatabase

devonkyle
Starting Member

19 Posts

Posted - 2003-10-29 : 15:29:32

Great !! - that seems to resolve the problem - amazing what 1 character can do -
question - I'm using this technique for an online Web based application that will be supporting many concurrent connections -

Will using a ##globaltemp table create any special concerns vs using a
#localtemp table ?
The scope of a ##global table is still unique for each individual user right?
thanks

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2003-10-29 : 15:34:24

Local temporary tables are visible only in the current session; global temporary tables are visible to all sessions
you should consider naming the ## table some unique value which you can associate to a particular user or session.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources