caution in dropping a temp table before creating it
Recently I ran into a script instead a stored procedure
IF OBJECT_ID(tempdb..#temp1) DROP TABLE #temp1
Basically, the object of this script is to check if #temp1 (regular temporary table) exists. If so drop it.
However, I think it can have unintended consequences and maybe safer not to include. [more]
Say you have a script that includes the call to the stored procedure (eg. SampleStoredProc)
If the script (let’s call this “caller”) creates a table #temp1 and at
the top of SampleStoredProc you have if object_id(tempdb..#temp1) drop
table #temp1, what will happen is that the #temp1 table of the “caller”
will be dropped.
And the caller might not want that (or won’t expect that the #temp1
table he/she created will be dropped). It is possible that after
calling SampleStoredProc the caller would still want to use/access
#temp1.
On the other hand if no drop table #temp1 is executed inside
SampleStoredProc and a create table #temp1 is made even if the “caller”
has a #temp1 already it will not be a problem. The #temp1 of the caller
and #temp1 of BehavClusDOM1 will be separately identified.
Since BehavClusDOM1 is a stored procedure it is a scope for temp tables
and safe to assume that at the start of the stored procedure no temp
tables are present in that scope.
Basically the idea is that BehavClus should not touch whatever is beyond its scope.
So in my opinion the inclusion of this code can cause unexpected
behavior to the caller while removing it poses no risk not to mention
shortening the code and decreasing complexity and readability. The
author of the stored procedure (eg. SampleStoredProc) involving
creation of #temp1 should know when it is present and shouldn't worry
about clashing with another #temp1 in another session. You can
explicitly DROP TABLE #temp1 if you want but only after you have
created your own #temp1 so your sure that you'd be dropping the one you
created and not that in other scripts.
FROM : .NET Develoer Notes on caution in dropping a temp table before creating it