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:
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.
answered Aug 24, 2010 at 06:18 PM
Grant Fritchey ♦♦
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.
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, 2010 at 11:50 PM
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
Real easy-like that change was :-). But what if the original code didn't have the functionally-superfluous
Guess what? The
The moral of this story: Yes, the
How many people, looking at that real quickly, especially due to where the blank lines are, would misread that and think that that the
Regarding Example 3 and not having the