SQL Server, can't check if #temporary table exists, with if else statement

Question

I'm creating a Trigger that handles a temp table (#attributeType). Since the trigger can be called more than once, I wanted to be sure and check if the temp table #attributeType is still there.

This is my code in the trigger body that checks for the temp table:

IF OBJECT_ID('tempdb..#attributeType') IS NOT NULL
    BEGIN
        DROP TABLE #attributeType
        SELECT * INTO #attributeType
        FROM attributeType
        PRINT 'IN IF'+   CAST(OBJECT_ID('tempdb..#attributeType') AS NVARCHAR(80))
    END
ELSE
    BEGIN
        SELECT * INTO #attributeType
        FROM attributeType
        PRINT 'IN ELSE'+  CAST(OBJECT_ID('tempdb..#attributeType') AS NVARCHAR(80))
    END

When I test the code by selecting it with F5 I get this error message, stating that the #attributeType temp table exist:

Msg 2714, Level 16, State 1, Line 11
There is already an object named '#attributeType' in the database.

I know that in stored procedures the #tempTables will be deleted as soon as the sp ends, but still I can't understand why my code is wrong?

N.B. When I comment out the ELSE block the code works.

Answer 1

Your test on OBJECT_ID('tempdb..#attributeType') works fine. You can test it by running it separately, without a drop and create in the same batch.

The problem is that SQL Server parses the entire batch before it runs it. If it knows that #attributeType exists it will give an error for:

SELECT * INTO #attributeType

Even if you drop the table in the previous row.

One solution is to drop the table in a different batch:

IF OBJECT_ID('tempdb..#attributeType') IS NOT NULL
    DROP TABLE #attributeType
GO -- new batch
SELECT * INTO #attributeType ...

Another solution is to create the table in a different scope:

IF OBJECT_ID('tempdb..#attributeType') IS NOT NULL
    DROP TABLE #attributeType
exec ('SELECT * INTO #attributeType ...')