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