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

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

  • Hi Mike,

    Thanks for this tip.

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

    Happy New Year !

    Regards, Paul

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

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

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

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

  • 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)
    Intro to Tally Tables and Functions

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

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