Dropping table in Sql-Server using try-catch block

Question

The code below works and is quite precise, but is it OK to do it like this as against the other 'standard' ways ?

--Drop table if exists
begin try
    drop table #temp
end try

begin catch 
    print 'table does not exist'
end catch

--Create table
create table #temp(a int, b int)

It all depends on your requirements. If you are just working with a temporary table then Pream's answer is as good as any. Your technique would give feedback on whether the table existed, and that feedback may be useful — Daniel Hollinrake
– Daniel Hollinrake, Commented Mar 6, 2017 at 10:17
Catch blocks are brilliant if you intend to take action when/if an exception occurs. If there is no additional action, you could argue it's just extra typing. In this case, I often don't care if the temp exists or not. I am only checking to avoid an error. With that in mind, the IF NOT EXISTS/OBJECT_ID IS NULL methods are more concise. — David Rushton
– David Rushton, Commented Mar 6, 2017 at 10:22
@destination-data Looks like Try catch is overkill here. If one needs additional actions when table doesn't exist if ... begin additional-actions end will do. — Serg
– Serg, Commented Mar 6, 2017 at 10:27
I am sorry I did not mean just temporary tables, I just want feedback on whether this would be a good way to drop-create any table including temp ones. — anonxen
– anonxen, Commented Mar 6, 2017 at 11:37

Pream · Accepted Answer · 2017-03-06 10:36:01Z

6

It is better to use

If Object_Id('Tempdb..#temp') Is Not Null
Drop Table #temp
create table #temp

As you intend to create a #temp Table ultimately which does not require try catch to give a error message that #temp Table does not exists

if the create statement was inside the try, it may have some use

edited Mar 6, 2017 at 10:36

answered Mar 6, 2017 at 10:12

Pream

5374 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

David Rushton Over a year ago

Why? I'm not necessarily disagreeing, but can you explain the benefits this approach offers?

Pream Over a year ago

Hi @destination-data, By the look of the code in the question, anonxen ultimately wants to create a temp table, which does not require try catch to give a error message that tempTable does not exists, in case if the create statement was inside the try, it may have some use

David Rushton Over a year ago

If your answer included that description, I'd definitely upvote it.

Pream Over a year ago

@destination-data, There you go!

DK5 · Accepted Answer · 2017-03-06 11:05:05Z

0

Use EXISTS statement, IF tables exists then only drop table. Otherwise create table directly:

BEGIN TRY
  IF Object_Id('Tempdb..#temp') Is Not Null
     DROP Table #temp
  CREATE table #temp
END try
BEGIN CATCH
  PRINT 'table does not exist'
END CATCH

edited Mar 6, 2017 at 11:05

DK5

3023 silver badges15 bronze badges

answered Mar 6, 2017 at 10:15

Mansoor

4,1921 gold badge19 silver badges27 bronze badges

1 Comment

Christian Over a year ago

I'm getting older, I even started using glasses around the house and not just in front of the computer. I other words, I could be getting blind, but where exactly is EXISTS used in this suggestion? it says not null :)

Collectives™ on Stack Overflow

Dropping table in Sql-Server using try-catch block

2 Answers 2

4 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related