Should I check for existence of temp tables in Stored Procedures?

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?

On Checking for #tbl

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.

On DROP TABLE #tbl;

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:

• Temporary Tables in Stored Procedures

• Temporary Table Caching Explained

StackExchange Database Administrators Q#73514, answer score: 8