asked
Dropping a temp table
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?
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.
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.
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
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.
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.
You can also try this IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE ID = OBJECT_ID(N'tempdb..#tablenm')) BEGIN DROP TABLE #tablenm END
@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
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
Method 1 and Method 2 are _functionally_ equivalent. However, the real difference between them is important as it is a matter of _maintenance_. Consider that over the course of several years there will likely be changes made to this code, and likely by someone who did not code it originally. So the desire might be to add a flag for some reason to indicate that this operation needed to be performed (just a contrived yet simple example). So the change would be to add a BIT variable for `@DroppedTempTable = 0` and a `SET @DroppedTempTable = 1;` statement if that IF condition evaluates to "true". That should look like: DECLARE @DroppedTempTable BIT = 0; IF (OBJECT_ID(N'tempdb..#some_temp_tbl') IS NOT NULL) BEGIN DROP TABLE #some_temp_tbl; SET @DroppedTempTable = 1; END; Real easy-like that change was :-). But what if the original code didn't have the functionally-superfluous `BEGIN` / `END` markers _and_ Over-Worked-Developer-With-Less-Than-Stellar-Attention-To-Detail makes the change quickly, 5 minutes after the sprint ends and the Release Engineering team has declared "Code Freeze" so that they can push the changes to Staging, and Over-Worked-Developer does _not_ want to go ask a manager to intervene so that this one last change can get in. That could look like: DECLARE @DroppedTempTable BIT = 0; IF (OBJECT_ID(N'tempdb..#some_temp_tbl') IS NOT NULL) DROP TABLE #some_temp_tbl; SET @DroppedTempTable = 1; Guess what? The `SET` statement will _always_ execute since it is not tied to the `IF` statement. SQL Server doesn't care about indentations or intention ;-). The moral of this story: Yes, the `BEGIN` / `END` tags are functionally extraneous on `IF` and `WHILE` statements when the action is a single statement/query. HOWEVER, at the same time, those `BEGIN` / `END` tags will _never_ be functionality incorrect! And having them there almost guarantees that any updates made in the future to include more than 1 action dependent upon the `IF` being "true" will be done correctly the _first_ time, thereby cutting down on easily-avoidable wasted QA time and code fixing and redeployment time. Having the `BEGIN` / `END` tags also assists in readability for someone quickly scanning the code when trying to determine what it is doing (either for support or scoping out new functionality and looking for what needs to change, etc). Assume that the following code is correct: DECLARE @DroppedTempTable BIT = 0; IF (OBJECT_ID(N'tempdb..#some_temp_tbl') IS NOT NULL) DROP TABLE #some_temp_tbl; SET @DroppedTempTable = 1; EXEC dbo.SomeProc; How many people, looking at that real quickly, especially due to where the blank lines are, would misread that and think that that the `DROP` and `SET` are both dependent upon the `IF`? Certainly some. But adding in the functionally extraneous `BEGIN` / `END` tags would almost guarantee that nobody would misread it. That is certainly worth the time it takes to add in those 8 extra characters, plus the 2 extra returns, but some spaces and/or tabs ;-). Regarding Example 3 and not having the `IF` check at all, that is just bad / sloppy coding. It doesn't really matter if typically SQL Server would not halt execution on the error. Clean, good code should handle errors appropriately. What if someone adds `SET XACT_ABORT ON;` above that code, in the same proc or a calling proc? What about app code that might be trapping a `SqlException`? I don't think such an error would merely look like a `PRINT` statement (i.e. informational only). Regardless, knowing that an error can occur but intentionally not dealing with it because execution at least continues is just bad coding. Exceptions happen, and they should be handed, even if that handling is to merely swallow them and move on to the next statement. Ignoring the error should be intentional and not merely a side-effect.
1 Person is following this question.
