Strange "There is already an object in the database." error

Question

I have the following SQL code:

IF OBJECT_ID( 'tempdb..#PropList') IS NOT NULL
  DROP TABLE #PropList

DECLARE @Split CHAR(1), @propList NVARCHAR(MAX), @PropListXml XML
SET @Split = ','
SET @propList = 'NAME,DESCRIPTION'
-- SET @propList = ''

IF (@propList IS NOT NULL AND @propList != '')
  BEGIN
    SET @PropListXml = CONVERT(XML,'<root><s>' + REPLACE(@propList, @Split, '</s><s>') + '</s></root>')

    SELECT SystemName = T.c.VALUE('.','nvarchar(36)')
    INTO #PropList
    FROM @PropListXml.nodes('/root/s') T(c)
  END
ELSE
  BEGIN
    SELECT SystemName
    INTO #PropList -- Stops here
    FROM tblProperty
  END

SELECT * FROM #PropList

Regardless of the value of @propList, this code always stops at the indicated line with this error:

There is already an object named '#PropList' in the database.

My expectation was that only one of the two IF blocks is executed, therefore there should be only one attempt to create the table with the SELECT... INTO statement. Why is this failing?

Try explicitly creating `#PropList` before the `IF`, then do an `INSERT INTO #PropList SELECT ...`. — Chris Pickford, Jun 16 '16 at 10:21
@ChrisPickford: That worked for me. I am just puzzled why it is not working like this — Moslem Ben Dhaou, Jun 16 '16 at 10:24
This error is generated during code parsing (and before execution) and code parsing does not execute control flow statements (as you would expect). Use @Chris Pickford answer to work around the problem. — Alex, Jun 16 '16 at 10:25
Similar question: http://stackoverflow.com/questions/4828261/why-does-sql-server-thinks-a-temp-table-already-exists-when-it-doesnt — Alex, Jun 16 '16 at 10:33

score 2 · Accepted Answer · edited May 23 '17 at 11:58

As per comment, you'll need to explicitly define your #temp table before the IF statement, then change your

SELECT ... INTO #temp

to be

INSERT INTO #temp SELECT ...

This is because when SQL Server validates the query it ignores any control flow statements. For more detail on this see the following SO question:

T-Sql appears to be evaluating "If" statement even when the condition is not true

Strange "There is already an object in the database." error

1 Answers1