temp table issue - 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)

temp table issue

Author

Topic

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2004-10-21 : 17:17:32

How can I check for a condition like this for the temp table

if exists (select * from dbo.sysobjects
where id = object_id(N'#temp_info') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE #temp_info

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-10-21 : 17:23:42

Why wouldn't you know if the temp table exists already?

Tara

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-10-21 : 18:46:50

Temporay tables are created in tempdb,
so you must query tempdb.dbo.sysobjects

rockmoose
/* Chaos is the nature of things...Order is a lesser state of chaos */

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2004-10-21 : 19:15:23

I checked this ..this is not droping the table

if exists (select * from tempdb.dbo.sysobjects
where id = object_id(N'#temp_info') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE #temp_info
else
print 1

nr
SQLTeam MVY

12543 Posts

Posted - 2004-10-21 : 22:18:36

if object_id(N'tempdb.dbo.#temp_info') is not null

But I would go with Tara's comment.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2004-10-22 : 15:15:49

This works....Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-10-22 : 15:16:50

sqllearner, you never answered my question...What code are you writing that would require this check?

Tara

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-10-22 : 17:39:30

I don't know about sqllearnr but I do this all the time. When I'm writing stored procedures and I want to select and execute parts of it, I put 'if object_id... drop..' so I can repeatedly execute and test parts of the procedure without dropping temp tables created by the last run.

--Ken
I want to die in my sleep like my grandfather, not screaming in terror like his passengers.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2004-10-22 : 17:50:56

I do the same thing.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-10-22 : 17:54:24

But for production code or just testing?

Tara

robvolk
Most Valuable Yak

15732 Posts

Posted - 2004-10-22 : 18:17:12

I've kept it in some production code, just to be sure I don't create a temp table that already exists. We also use it to create the temp table if it doesn't already exist. It's very handy if you have sprocs calling other sprocs, especially based on IF conditions.

nr
SQLTeam MVY

12543 Posts

Posted - 2004-10-23 : 15:42:53

>> I've kept it in some production code, just to be sure I don't create a temp table that already exists.

Only works if the temp table has the same structure as the one being dropped otherwise you will get an error or incorrect results. Better to not do this and using the scoping rules.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-10-23 : 16:13:09

This used to be standard practice in 6.5 days everyone. I still have it in a lot of my old code. For maintenance procs, I don't see any reason to take it out. I like being able to chop off the header and run things repeatedly. For production code, it can cause some serious recompiles though.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

nr
SQLTeam MVY

12543 Posts

Posted - 2004-10-23 : 16:24:43

>> This used to be standard practice in 6.5 days everyone.
Really - at your place maybe but...

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-10-23 : 17:02:03

I've seen code at every company I worked at back then that used it. So, it wasn't just "my place". MCI ring a bell. I wasn't the one determining how people would code there.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2004-10-23 : 17:09:01

quote:
Only works if the temp table has the same structure as the one being dropped otherwise you will get an error or incorrect results.

Not if I just want to drop that table and create a brand new one. In any event, 99% of these sprocs are chained a certain way and will utilize the same table with the same structure, so it's not an issue for me. It's a nice safeguard to prevent an error.

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2004-10-23 : 20:20:05

Sorry Tara, I was out for 2 days ..Iam using this type of code becasue instead of creating a table to get all the information from different tables i use a temp table and then insert it into the temp table and then perform several operations and once all the operations are done i will drop it..This goes to the production as well.Here I use this specifically becasue Iam using transactions and also while loop and when there is a roll back I will drop it and in other case First part of the code is an update statement outside transation.So if it fails..it goes to the place where Iam droping the temp table at the error part.So Iam using this as If its present.... drop it or don't drop it...

nr
SQLTeam MVY

12543 Posts

Posted - 2004-10-23 : 22:32:36

quote:
Originally posted by robvolk

quote:
Only works if the temp table has the same structure as the one being dropped otherwise you will get an error or incorrect results.
Not if I just want to drop that table and create a brand new one. In any event, 99% of these sprocs are chained a certain way and will utilize the same table with the same structure, so it's not an issue for me. It's a nice safeguard to prevent an error.

I sense danger - try this. Works without the drop but will error with it.

create proc a
as
create table #test (i float, j float)
insert #test select 1.1, 1.1
select * from #test
exec b
select * from #test
go
create proc b
as
if object_id('#test') is not null
drop table #test
create table #test (i int, j int)
insert #test select 1, 1
select * from #test
go

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-10-23 : 23:23:54

??? I just ran both of those about 20 times without any errors.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

nr
SQLTeam MVY

12543 Posts

Posted - 2004-10-24 : 14:31:48

Run it in tempdb or change
if object_id('#test') is not null
to
if object_id('tempdb.dbo.#test') is not null

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-10-24 : 21:53:44

Create the procs in tempdb???? That's not realistic, so I'll stick to the fact it works.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources