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