vendredi 4 juin 2021

SQL ELSE block still executes when IF condition is true

I have the following T-SQL script that copies the value of an old column into a new one, then drops the old column. See here:

--step 1: create new column
IF NOT EXISTS(SELECT 1 from sys.columns
                WHERE Name = N'UserColumn2'
                AND Object_ID = Object_ID(N'Account'))
    BEGIN
        ALTER TABLE Account
        ADD UserColumn2 int null
        ;
    END
GO
;

--step 2: copy and drop
IF NOT EXISTS(SELECT 1 from sys.columns
        WHERE Name = N'UserColumn1'
        AND Object_ID = Object_ID(N'Account'))
    BEGIN 
        PRINT 'Column ''UserColumn1'' does not exist.';
    END
ELSE
    BEGIN
        UPDATE Account
        SET UserColumn2 = UserColumn1
        WHERE UserColumn1 is not null
        ;

        BEGIN TRY
            Declare @count int;
            SELECT @count = Count(AccountID)
            FROM Account
            WHERE UserColumn2 <> UserColumn1
            ;
            IF @count > 0
                BEGIN 
                    --PRINT 'Not all records were properly updated. UserColumn1 has not been dropped.';
                    THROW 50000,'Not all records were properly updated. UserColumn1 has not been dropped.',1
                    ;
                END
            ELSE
                BEGIN
                    ALTER TABLE Account
                    DROP Column UserColumn1
                    ;
                END
        END TRY
        BEGIN CATCH THROW; END CATCH
    END
GO
;

The first step runs correctly but the second step still executes the ELSE block even if the UserColumn1 column doesn't exist, throwing an error:

enter image description here
(note: this actually throws on line 24 for the code here. The code in my SSMS doesn't have the comments for 'step 1', etc.)

Why is this happening and how can I prevent it?

I've tried removing the NOT and moving the instructions out of the ELSE block but the behavior did not change. I've also tried writing the beginning of the second step like this:

IF (SELECT 1 from sys.columns
        WHERE Name = N'UserColumn1'
        AND Object_ID = Object_ID(N'Account')) <> null

and I get the same result.

Aucun commentaire:

Enregistrer un commentaire