CODEWITHSUNDEEP
sql-servert-sql
Moslem Ben Dhaou
Moslem Ben Dhaou

Reputation: 7005

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

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?

Upvotes: 3

Views: 470

Answers (1)

Chris Pickford
Chris Pickford

Reputation: 9001

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

Upvotes: 2

Related Questions