SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Mike Arney / One line to drop a (temp) table if it exists / Latest Posts

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

Jeff Moden — Thu, 16 Apr 2009 12:01:37 GMT

[quote][b]Mike Arney (4/15/2009)[/b][hr]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.[/quote]That's because, behind the scenes, the local temp table isn't named simply "#t". It'll be something like...#t_________________________________________________________________________________________________________12A52C39F0That'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.

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

Mike Arney — Wed, 15 Apr 2009 08:31:30 GMT

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:[code]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;[/code]... but this will be caught:[code]begin try raiserror('higher severity error, will be caught', 11, 1); end try begin catch select 'catching'; end catch;[/code]Thanks everyone for your comments.

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

Jim Russell-390299 — Wed, 15 Apr 2009 07:19:03 GMT

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.

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

tfountain — Tue, 14 Apr 2009 11:18:09 GMT

Seems like a nifty trick but I prefer the old fashioned way:IF OBJECT_ID('tempdb..#t') IS NOT NULL DROP TABLE #t;

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

Paul Chu — Fri, 26 Dec 2008 12:23:04 GMT

Hi Mike,Thanks for this tip.Didn't know Sql now has a Try / Catch.Happy New Year !Regards, Paul

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

Mike Arney — Tue, 07 Oct 2008 06:23:29 GMT

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