Temporary table both exists and doesn't exist

Question

I'm trying to drop a temporary table in SQL Server if it exists and create a new temp table if it doesn't exist. The command IF OBJECT_ID('tempdb.. #my_temp') IS NULL seems unable to fine the temp table and then when the query tries to create the table, I receive an error that the temp table already exists.

This is my query:

IF OBJECT_ID('tempdb.. #my_temp') IS NOT NULL  
BEGIN 
DROP TABLE  #my_temp 
END

IF OBJECT_ID('tempdb.. #my_temp') IS NULL
BEGIN
print 'trying to create a new temp table'
CREATE TABLE   #my_temp
( [ID] int NOT NULL PRIMARY KEY CLUSTERED ,
) ON [PRIMARY]
END

If I run this query twice, on the second time I receive this output:

trying to create a new temp table
Msg 2714, Level 16, State 6, Line 12
There is already an object named '#my_temp' in the database.

My SQL Server is version 2019 for your info.

Answer 1

Remove the space between .. and the table name

IF OBJECT_ID('tempdb..#my_temp') IS NOT NULL  
BEGIN 
DROP TABLE  #my_temp 
END

IF OBJECT_ID('tempdb..#my_temp') IS NULL
BEGIN
print 'trying to create a new temp table'
CREATE TABLE   #my_temp
( [ID] int NOT NULL PRIMARY KEY CLUSTERED ,
) ON [PRIMARY]
END