How can I update a newly added column in the same batch statement

Question

In SQL Server 2008, I would like to add a column to a table and update it right after, but only if they column hasn't been created before. I do NOT want to run the update if the column has been created before.

IF NOT EXISTS (SELECT *
               FROM [INFORMATION_SCHEMA].[COLUMNS] 
            WHERE [TABLE_NAME] = 'SETTINGS' AND [COLUMN_NAME] = 'COLOR_SCHEME')
BEGIN
    ALTER TABLE [SETTINGS]
    ADD [COLOR_SCHEME] int NULL

    UPDATE [SETTINGS]
    SET [COLOR_SCHEME] = 1
END

Putting a "GO" after the column add doesn't work because that wouldn't be a complete batch statement, but if I try to run it like this, i get the error, "Invalid column name 'COLOR_SCHEME'."

Any ideas on how to get the column to exist when the update is run?

Answer 1

You could try using dynamic SQL instead for the alter statement:

DECLARE @SQL NVARCHAR(4000)
SET @SQL='ALTER TABLE [SETTINGS] ADD [COLOR_SCHEME] int NULL'
EXEC(@SQL)

Answer 2

Another possibility is to save the IF criteria across batches:

CREATE TABLE ##Temp_Add_Color_Scheme (new_column BIT)
INSERT INTO ##Temp_Add_Color_Scheme VALUES (0)

IF NOT EXISTS (SELECT *
               FROM [INFORMATION_SCHEMA].[COLUMNS] 
               WHERE [TABLE_NAME] = 'SETTINGS' AND
                     [COLUMN_NAME] = 'COLOR_SCHEME')
BEGIN
    UPDATE ##Temp_Add_Color_Scheme SET new_column = 1

    ALTER TABLE [SETTINGS]
    ADD [COLOR_SCHEME] int NULL
END
GO

DECLARE @new_column BIT
SELECT @new_column = new_column FROM ##Temp_Add_Color_Scheme

IF (@new_column = 1)
BEGIN
    UPDATE [SETTINGS]
    SET [COLOR_SCHEME] = 1
END

DROP TABLE ##Temp_Add_Color_Scheme

Answer 3

If the content of the column is fixed, can you not simply put a default value in it instead of updating it?

ALTER TABLE [SETTINGS] 
    ADD [COLOR_SCHEME] int NULL 
    DEFAULT 1 WITH VALUES ;