One line to drop a (temp) table if it exists

Question

Post reply

One line to drop a (temp) table if it exists

Mike Arney

SSChasing Mays

Points: 617
More actions
October 7, 2008 at 6:23 am

#198947

Comments posted to this topic are about the item One line to drop a (temp) table if it exists

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Paul Chu SSC Journeyman Points: 90 More actions · Answer 1

Hi Mike,

Thanks for this tip.

Didn't know Sql now has a Try / Catch.

Happy New Year !

Regards, Paul

tfountain SSC Enthusiast Points: 106 More actions · Answer 2

Seems like a nifty trick but I prefer the old fashioned way:

IF OBJECT_ID('tempdb..#t') IS NOT NULL DROP TABLE #t;

Jim Russell-390299 SSCrazy Eights Points: 9333 More actions · Answer 3

I think I recall failing to get a try/catch block to work (but likely not with a #temp table) because (I thought) the error returned was not at a high enough level.

Any idea what I might have tripped over.

Mike Arney SSChasing Mays Points: 617 More actions · Answer 4

First, thanks for the comment about object_id('tempdb..#t'): I did not realize that would reliably work. I was afraid if 2 sessions both had a #t, object_id() would have the same problem as something like "select * from tempdb.sys.objects where name like '#t%' ", namely, it would not distinguish between the different #t's in the different sessions. Seems that it does that just fine.

As far as the catch not working due to insufficiently severe errors, the drop of a non-existent table raises a severity 11 error. According to BOL (topic "TRY...CATCH"): "A TRY…CATCH construct catches all execution errors that have a severity higher than 10 that do not close the database connection.". So these errors are not caught:

begin try raiserror('severity 10 error, will NOT be caught', 10, 1); end try begin catch select 'catching'; end catch;

begin try print 'this print will print'; end try begin catch select 'catching'; end catch;

... but this will be caught:

begin try raiserror('higher severity error, will be caught', 11, 1); end try begin catch select 'catching'; end catch;

Thanks everyone for your comments.

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 5

Mike Arney (4/15/2009)
First, thanks for the comment about object_id('tempdb..#t'): I did not realize that would reliably work. I was afraid if 2 sessions both had a #t, object_id() would have the same problem as something like "select * from tempdb.sys.objects where name like '#t%' ", namely, it would not distinguish between the different #t's in the different sessions. Seems that it does that just fine.

That's because, behind the scenes, the local temp table isn't named simply "#t". It'll be something like...

#t_________________________________________________________________________________________________________12A52C39F0

That's what allows local temp table to be session specific and also prevents mutual interfernce between sessions that all want to use a #t table.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)