Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

One line to drop a (temp) table if it exists Expand / Collapse
Author
Message
Posted Tuesday, October 7, 2008 6:23 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, May 15, 2014 6:04 AM
Points: 91, Visits: 108
Comments posted to this topic are about the item One line to drop a (temp) table if it exists
Post #581724
Posted Friday, December 26, 2008 12:23 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Tuesday, April 14, 2009 11:18 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC 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
Posted Wednesday, April 15, 2009 7:19 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-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
Posted Wednesday, April 15, 2009 8:31 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, May 15, 2014 6:04 AM
Points: 91, Visits: 108
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
Posted Thursday, April 16, 2009 12:01 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 9:39 PM
Points: 36,611, Visits: 31,044
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #698744
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse