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

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Mike Arney » One line to drop a (temp) table if it exists

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

Rate Topic

Display Mode

Topic Options

Author

Message

Mike Arney

Posted Tuesday, October 07, 2008 6:23 AM

SSC Journeyman

Group: General Forum Members
Last Login: Thursday, May 09, 2013 3:47 PM
Points: 90, Visits: 82

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

Post #581724

Paul Chu

Posted Friday, December 26, 2008 12:23 PM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, April 20, 2011 6:00 PM
Points: 4, Visits: 22

Hi Mike,

Thanks for this tip.
Didn't know Sql now has a Try / Catch.

Happy New Year !

Regards, Paul

Post #625999

tfountain

Posted Tuesday, April 14, 2009 11:18 AM

SSC Rookie

Group: General Forum Members
Last Login: Friday, October 30, 2009 7:22 AM
Points: 44, Visits: 50

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

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

Post #696813

Jim Russell-390299

Posted Wednesday, April 15, 2009 7:19 AM

SSC-Addicted

Group: General Forum Members
Last Login: Friday, July 30, 2010 11:27 AM
Points: 435, Visits: 1,403

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.

Post #697491

Mike Arney

Posted Wednesday, April 15, 2009 8:31 AM

SSC Journeyman

Group: General Forum Members
Last Login: Thursday, May 09, 2013 3:47 PM
Points: 90, Visits: 82

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.

Post #697580

Jeff Moden

Posted Thursday, April 16, 2009 12:01 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:30 PM
Points: 32,893, Visits: 26,770

Mike Arney (4/15/2009)

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."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #698744

« Prev Topic | Next Topic »

Permissions