SQL multiple if's not running on DbUp-CodePudding

The following is my code

IF NOT EXISTS  (SELECT 1 FROM sys.objects o INNER JOIN sys.columns c ON o.object_id = c.object_id
    WHERE
        o.name = 'portfolioAttributeCodes'
        AND c.name = 'isDisplayed'
)
BEGIN
    ALTER TABLE
        [cosmos].[portfolioAttributeCodes]
        ADD
        [isDisplayed] bit DEFAULT 1;
END
IF EXISTS  (SELECT 1 FROM sys.objects o INNER JOIN sys.columns c ON o.object_id = c.object_id
    WHERE
        o.name = 'portfolioAttributeCodes'
        AND c.name = 'isDisplayed')

BEGIN
    UPDATE [cosmos].[portfolioAttributeCodes] SET [isDisplayed] = 1;        
END

Now what is happening is it will not create a column (skips the first Id statement and gets into the second one and fails with Invalid column name 'isDisplayed'

Can some one help?

CodePudding user response：

If the table doesn't have the column isDisplayed already the entire batch will fail, as the parser will generate an Invalid column name error. This occurs before any of the SQL is run, so it's not that the second IF is being entered, none of the SQL is run at all. It's effectively a compilation error (like when you try to build you C# application and you have a reference to an object you haven't defined).

You cannot reference a new column in the same scope it was created. You would need to use 2 batches or put the reference to the column in a separate scope, so that its validation is deferred.

A deferred validation would seem fine here:

IF NOT EXISTS (SELECT 1
               FROM sys.objects o
                    INNER JOIN sys.columns c ON o.object_id = c.object_id
               WHERE o.name = 'portfolioAttributeCodes'
                 AND c.name = 'isDisplayed')
BEGIN
    ALTER TABLE [cosmos].[portfolioAttributeCodes]
    ADD [isDisplayed] bit CONSTRAINT DF_isDisplayed DEFAULT 1 WITH VALUES;
END;
ELSE
BEGIN
    EXEC sys.sp_executesql N'UPDATE [cosmos].[portfolioAttributeCodes] SET [isDisplayed] = 1;';
END;

I also switch to an ELSE as there is little point updating the column after you've created it; just create the column with the values in the first place. I name the DEFAULT CONSTRAINT as well as that's just good habit.