Table “already exists” after dropping with if exists

I got a pretty complex SQL that finally forced me to use a temp table to work around.

Essentially it looks like this:

;IF EXISTS(SELECT * FROM sys.tables WHERE SCHEMA_NAME(schema_id) LIKE 'dbo' AND name like '#MYTEMPTABLE')
DROP TABLE #MYTEMPTEBLE;

WITH cte AS ...
SELECT * INTO #MYTEMPTABLE FROM cte
SELECT * FROM #MYTEMPTABLE WHERE [conditions]
DROP TABLE #MYTEMPTABLE;

However, I get an error message saying an object with the name #MYTEMPTABLE already exists in the database after a call with an error (which is rather likely if the customer/tester screws up some data).

Answer

It might DROP TABLE fail on your check condition, it might check from TempDB.INFORMATION_SCHEMA.COLUMNS table instead of sys.tables table

SELECT * FROM TempDB.INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME IN (
 SELECT NAME 
 FROM TempDB.SYS.TABLES 
 WHERE OBJECT_ID=OBJECT_ID('TempDB.dbo.#MYTEMPTEBLE')
);

sqlfiddle

If your SQL server version was higher than 2016, you can try to use DROP TABLE IF EXISTS

DROP TABLE IF EXISTS #MYTEMPTEBLE;
WITH cte AS ...
SELECT * INTO #MYTEMPTABLE FROM cte
SELECT * FROM #MYTEMPTABLE WHERE [conditions]

if your SQL server version didn’t support that, you can check OBJECT_ID IS NOT NULL which represnt temp table exists in your system

IF OBJECT_ID('TempDB..#MYTEMPTEBLE') IS NOT NULL
DROP TABLE #MYTEMPTEBLE;