Dynamically created temp table

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)

Dynamically created temp table

Author

Topic

Karander
Starting Member

34 Posts

Posted - 2004-09-23 : 07:23:10

Hi!
I create session table like this:

select @cg = 'create table #temp1 (wr varchar(10), a int)'
exec (@cg)
then i can not select that table (it doens;t work)
i got: Invalid object name, doing select * from #temp1

if there is a normal table without hash it is ok, but
I need session table, so what can i do??

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-23 : 07:26:41

use double ##
select @cg = 'create table ##temp1 (wr varchar(10), a int)'
exec (@cg)

that's because ##temp is a global temp table that can be accessed from multiple connections. #temp can't.

Go with the flow & have fun! Else fight the flow

Karander
Starting Member

34 Posts

Posted - 2004-09-23 : 07:44:04

Hi!
Thanks, it works.
Please tell me also how to check if that ##temp1 exists in system
and drop table. I tried sth like this, but it doesn;t work:

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[##temp1]') ) drop table ##temp1

Really thanks for help

quote:
Originally posted by spirit1

use double ##
select @cg = 'create table ##temp1 (wr varchar(10), a int)'
exec (@cg)

that's because ##temp is a global temp table that can be accessed from multiple connections. #temp can't.

Go with the flow & have fun! Else fight the flow

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-23 : 08:14:47

well you can always do this:
if exists(select top 1 1 from ##temp)begin
drop table ##temp
end

Go with the flow & have fun! Else fight the flow

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-09-23 : 08:15:48

but if the global ##temp isn't there, won't that error?

Corey

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-23 : 08:18:41

EDIT:
yes it will error

so this should do:

if exists(SELECT * FROM tempdb..sysobjects WHERE name LIKE '##temp')

Go with the flow & have fun! Else fight the flow

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-09-23 : 08:21:29

this works...


if exists(select * from tempdb.dbo.sysobjects where id = object_id(N'[tempdb].[dbo].[##temp]'))
Begin
	drop table ##temp
End
create table ##temp (id int identity(1,1), a varchar(1))

Corey

Karander
Starting Member

34 Posts

Posted - 2004-09-23 : 08:32:38

Works, Thank you very much !!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources