Is there any edge case in which it would be recommended to explicitly check for ,drop and create temp tables at the start of a Stored Procedure instead of just creating them?
Similarly, is there a case when explicitly dropping them at the end of the Stored Procedure is preferable to letting SQL Server clean them up?
It can't hurt to check for the table's existence (and drop it if it exists) at the beginning of the procedure, but it depends on how you want to handle that scenario, and in most cases it's not possible for it to exist already anyway (at least if we're talking about the same #temp table as defined within that stored procedure).
You check for a table's existence using:
IF OBJECT_ID('tempdb..#tablename') IS NOT NULL
You can't check tempdb.sys.tables because the actual name is #tablename__________some hex code, and you shouldn't use OBJECT_ID('...') > 0 because of this potential issue.
Of course, there are exceptions. Two that come to mind:
if you call everything #temp, or #t, or #x, then it's possible that such a table already exists from an outer scope prior to calling the procedure. You could make a case that you should just drop it and create your new one in that case, but you could also make the case that this is an error condition you want to know about - so maybe it's okay that CREATE TABLE #x fails.
you actually may want to use a #temp table created in an outer scope, and only create one if it hasn't been defined in that outer scope. This is possible and I use this technique all the time, but usually only when I want to capture data from system procedures that I can't easily manipulate myself (e.g. sp_helptext). So I might do this:
CREATE TABLE #x([Text] NVARCHAR(MAX));
GO
CREATE PROCEDURE dbo.myhelp @p SYSNAME
AS
INSERT #x EXEC sp_helptext @p;
GO
EXEC dbo.myhelp N'dbo.myhelp'; -- inception
GO
SELECT [Text] FROM #x;
This works even though #x is defined outside. That's a terrible example because I'd rather use sys.sql_modules, but I'm sure you get the point and can envision how you might do that with your own procedures.
I think whether you should explicitly drop #temp tables at the end of the procedure is very much up for debate, and hence would be closed as primarily opinion-based; see these excellent blog posts by Paul White, read them thoroughly, and come back and formulate a specific question if all yours aren't answered:
I think it would depend on how you are using temp tables. If SELECT INTO is being used I would test before and drop it at the end. Testing before hand and dropping will prevent unwanted issues down the road that may or may not creep up, it is just good code practice to me.
If you are using CREATE TABLE to first create the temp table I think I would do the same thing just out of habit and trying to keep a standard as to how I develop procedures. With this you could probably get by with just having the drop statement at the end of the procedure, have seen code done with just drops at the end.
If you are on a team, or even if it is just you, come up with a standard and stick to it.