Drop temp table

By definition, when a batch completes in SQL Server, any local temp tables created are automatically dropped. So then, if your stored procedure creates a local temp table such as #temp it will be dropped when the sp completes, and you don't need to include the statement DROP TABLE #temp to release resources in tempdb.

However, when you are developing a stored procedure, and the batch fails due to some type of error, the temp table will remain; if you re-run the batch you will get a message that #temp already exists, and you need to drop the temp table before you can run it again.

You can include a statement like this at the beginning of your batch, to prevent that from happening:

IF OBJECT_ID(N'tempdb..#temp', N'U') IS NOT NULL
DROP TABLE #temp