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.