CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2012
RLH
RLH

Reputation: 15698

IF EXISTS command appears to be executing, even when condition doesn't exist?

I have the following SQL statement within a large update script:

...
PRINT N'Updating Table_01, Applying TypeReassignment from YesNoField_01 data'
IF EXISTS (SELECT * FROM sys.columns WHERE object_id = OBJECT_ID(N'[dbo].[Table_01]') 
         AND name = 'YesNoField_01')
    BEGIN
        UPDATE Table_01 SET TypeReassignment = CASE YesNoField_01 WHEN 'Y' THEN 2 ELSE 1 END 
            WHERE TypeReassignment = 0
    END
 ... 
 //script continues with other modifications.  Towards the end of my script 
 //the YesNoField_01 field is dropped from the table.

The intent of the IF statement is to execute an UPDATE on a specific column only if another field exists on the table. As my comment at the end suggests, I am removing this original column (YesNoField_01) towards the end of my script.

The purpose of the IF check is so the script can be executed multiple times, even if it has been executed once before. If part of this script has been executed, it needs to skip over completed steps and continue execution without any errors. In this case, I need to skip over a data update.

However, if I execute my script on a database that has already had this script executed once before, I am getting the following error.

Invalid column name 'YesNoField_01'

When I navigate to the failing statement, it appears that the script is attempting to execute the UPDATE command within the IF block. When you independently execute the SELECT * FROM sys.columns WHERE object_id = OBJECT_ID(N'[dbo].[Table_01]') AND name = 'YesNoField_01' statement, you see that nothing is returned because the field YesNoField_01 has already been removed from the table.

I'm confused. If my SELECT statement does not result in an existing record, why am I getting an execution error from within the IF block? Or, a better question, why is it even trying to execute my IF block when the IF conditional should evaluate to false?

Upvotes: 1

Views: 1162

Answers (1)

Jim the Frayed
Jim the Frayed

Reputation: 158

Perhaps you could alter the test to check for the column's existence indirectly, thus:

DECLARE @YN_FieldName sysname =  
(
   SELECT Name 
   FROM sys.columns 
   WHERE object_id = OBJECT_ID(N'[dbo].[Table_01]') 
      AND name = 'YesNoField_01'
)

IF @YN_FieldName IS NOT NULL
   BEGIN
      UPDATE
         Table_01
      SET TypeReassignment = 
         CASE YesNoField_01
            WHEN 'Y' THEN 2
            ELSE 1
         END
      WHERE
         TypeReassignment = 0
   END

I suspect, though, that dynamic SQL will be required.

Upvotes: 1

Related Questions