dropping a temp table

Question

I've seen three different sets of syntax for dropping temp tables as a precaution before creating the same temp table, and I'm just wondering why you'd choose one method over another. They are:

METHOD 1

IF OBJECT_ID('tempdb..#some_temp_tbl') IS NOT NULL
  BEGIN
    DROP TABLE #some_temp_tbl;
  END

METHOD 2

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

METHOD 3

DROP TABLE #some_temp_tbl;

I presume method three means "We accept that we might generate the [Cannot drop the table '#some_temp_tbl', because it does not exist or you do not have permission.] error, but there's no harm done. Is it reasonable to assume that no harm will be done? As for methods 1 and 2, why would you choose one over the other?

Answer 1

0

Options 1 & 2 are basically the same. The BEGIN/END doesn't change anything. Some people just put them in automatically when dealing with IF statements. Since the IF statement will work with the very next statement, a BEGIN allows for more than one statement, but the option without is just doing the same thing.

answered Aug 24 '10 at 06:18 PM

Grant Fritchey ♦♦
64.9k ● 13 ● 20 ● 66

Thanks Grant. Is there any problem with dispensing with the IF statement altogether and just saying DROP TABLE (as in method 3)?

Aug 24 '10 at 07:44 PM GPO

It depends (I love that answer). If I was writing the code to create the temporary table, I could just drop without doing a check to see if it existed first. If, on the other hand, it's part of a more extensive set of code and I didn't know, for sure, that the temp table would exist when I called the DROP statement, no, I'd leave the IF in place.

Aug 25 '10 at 04:35 AM Grant Fritchey ♦♦

(comments are locked)

Answer 2

0

I agree with Grant. As you probably already know, if you try to drop a temp table or another object that doesn't exist you will trigger an exception / error. In SQL 2005 or later, you can use BEGIN TRY instead of the IF statement. I am not sure about the performance implication of this solution, but it gives you the option to handle other exceptions that might show up.

BEGIN TRY
    DROP TABLE #some_temp_tbl;
END TRY
BEGIN CATCH
    -- take care of the exception, you can even throw the message to the caller if you like
    PRINT ERROR_MESSAGE();
    PRINT ERROR_NUMBER();
END CATCH

IF statements (and heavy use of temptables) in a stored procedure may cause a recompile on every call, and that is not an option if you have thousands of requests / s

answered Aug 24 '10 at 11:50 PM

Håkan Winther
15k ● 29 ● 35 ● 46

Thanks Håkan,

The question is not so much about whether the error will be triggered, more about what the implications of triggering the error are. In SSMS it doesn't stop the subsequent code executing. Would this be the same where the code was executed by other processes? If there's no downside, why bother with the IF statement? Yes the error message exists, but is it of any consequence? I think that's what I'm getting at.

Aug 25 '10 at 12:48 AM GPO

SQL will continue the execution directly after the END CATCH block or directly after your statement if you don't use the TRY/CATCH syntax as you pointed out. But keep in mind that you could have a calling procedure that has a TRY/CATCH block, if it does, the exception will be caught and handled in that procedure. I would use the CATCH syntax to be able to trap all exceptions, just to be on the safe side.

Aug 25 '10 at 01:32 AM Håkan Winther

"...keep in mind that you could have a calling procedure that has a TRY/CATCH block..." Ah! That's the sort of thing I'm after. Greatly appreciated Håkan.

Aug 25 '10 at 01:53 AM GPO

+1 Good point.

Aug 25 '10 at 04:36 AM Grant Fritchey ♦♦

(comments are locked)

Answer 3

0

You can also try this

IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE ID = OBJECT_ID(N'tempdb..#tablenm')) 
BEGIN 
  DROP TABLE #tablenm 
END

answered Aug 25 '10 at 06:09 AM

V Padmala
83 ● 1 ● 3 ● 4

@V Padmala But that would be a bit too expensive. object_id check already does the trick, so there is no reason to issue a select. The actual reason to use the object_id is based on the simple fact that the actual table name (queried from sys.tables of the tempdb itself) is not equal to the name which was used to create it. Theoretically, it is always possible to drop the table by switching context to tempdb and then dropping the table by its actual name, but that would be dumb and dangerous as it allows dropping temp table when it is actively used by a different process (drop the wrong table in other words). What I mean is that if there is a proc which creates temp table and the proc is called by 5 sessions then there are 5 tables in tempdb, each with different (actual) name. object_id check allows checking existence of just the table which was (or not) created by the same session.

Just my 2 cents

Aug 25 '10 at 08:08 AM Oleg

Also keep in mind that dbo.sysobjects is deprecated and will be dropped in future releases of SQL server. It is replaced by sys.objects

Aug 25 '10 at 01:42 PM Håkan Winther

(comments are locked)

dropping a temp table

Follow this question

Related Questions