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
#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));
CREATE PROCEDURE dbo.myhelp @p SYSNAME
INSERT #x EXEC sp_helptext @p;
EXEC dbo.myhelp N'dbo.myhelp'; -- inception
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: