Test Column exists, Add Column, and Update Column

I'm trying to write a SQL Server database update script. I want to test for the existence of a column in a table, then if it doesn't exist add the column with a default value, and finally update that column based on the current value of a different column in the same table. I want this script to be runnable multiple times, the first time updating the table and on subsequent runs the script should be ignored. My script currently looks like the following:

IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME = 'PurchaseOrder' AND COLUMN_NAME = 'IsDownloadable')
BEGIN

ALTER TABLE [dbo].[PurchaseOrder] ADD [IsDownloadable] bit NOT NULL DEFAULT 0

UPDATE [dbo].[PurchaseOrder] SET [IsDownloadable] = 1 WHERE [Ref] IS NOT NULL

END

SQL Server returns error "Invalid column name 'IsDownloadable'", i.e. I need to commit the DDL before I can update the column. I've tried various permutations but I'm getting nowhere fast.

Solution 1:

This script will not run successfully unless the column already exists, which is exactly when you don't need it.

SQL Scripts have to be parsed before they can be executed. If the column doesn't exist at the time the script is parsed, then the parsing will fail. It doesn't matter that your scripts creates the column later on; the parser has no way of knowing that.

You need to put in a GO statement (batch separator) if you want to access a column that you just added. However, once you do that, you can no longer maintain any control flow or variables from the previous batch - it's like running two separate scripts. This makes it tricky to do both DDL and DML, conditionally, at the same time.

The simplest workaround, which I'd probably recommend for you because your DML is not very complex, is to use dynamic SQL, which the parser won't try to parse until "runtime":

IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME = 'PurchaseOrder' AND COLUMN_NAME = 'IsDownloadable')
BEGIN

    ALTER TABLE [dbo].[PurchaseOrder] ADD 
        [IsDownloadable] bit NOT NULL DEFAULT 0

    EXEC sp_executesql
        N'UPDATE [dbo].[PurchaseOrder] SET [IsDownloadable] = 1 WHERE [Ref] IS NOT NULL'

END

Solution 2:

I have often been annoyed by this problem myself, and unfortunately the solution suggested in Aaronaught's answer quickly becomes messy when @parameters and 'strings' are involved. However, I have found a different workaround by exploiting the usage of synonyms:

IF(COL_LENGTH('MyTable', 'NewCol') IS NULL)
BEGIN
    ALTER TABLE MyTable ADD NewCol VARCHAR(16) NULL;

    CREATE SYNONYM hack FOR MyTable;
    UPDATE hack SET NewCol = 'Hello ' + OldCol;
    DROP SYNONYM hack;

    ALTER TABLE MyTable ALTER COLUMN NewCol VARCHAR(16) NOT NULL;
END