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.
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
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.