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.

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

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

Try adding a "GO" statement after the ALTER TABLE.

It was news to me, but it says here that all statements in a batch (those preceeding the GO) are compiled into one query plan.) Withou no GO in the SQL, the entire plan is effectively one query.

EDIT: Since GO gives a syntax error (which seemed strange to me), I created something similar, and found this worked

declare @doUpdate bit;

SELECT @doUpdate = 0;

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

IF @doUpdate<>0 
   ALTER TABLE [dbo].[PurchaseOrder] ADD [IsDownloadable] bit NOT NULL DEFAULT 0

IF @doUpdate<>0
  UPDATE [dbo].[PurchaseOrder] SET [IsDownloadable] = 1 WHERE [Ref]=0

COMMIT TRAN

Although the accepted answer does work, for a more complicated case, you can use a temp table to persist data past the GO statement. just make sure you remember to clean it up after.

For example:

-- Create a tempTable if it doesn't exist. Use a unique name here
IF OBJECT_ID('tempdb..#tempTable') IS NOT NULL DROP TABLE #tempTable
CREATE TABLE #tempTable (ColumnsCreated bit)

-- Create your new column if it doesn't exist. Also, insert into the tempTable.
IF NOT EXISTS (
    SELECT * FROM   INFORMATION_SCHEMA.COLUMNS 
    WHERE  TABLE_NAME = 'targetTable' AND COLUMN_NAME = 'newColumn')
BEGIN
	INSERT INTO #tempTable VALUES (1)

	ALTER TABLE .dbo.targetTable ADD newColumn [SMALLINT] NULL ;
END

GO

-- If the tempTable was inserted into, our new columns were created.
IF (EXISTS(SELECT * FROM #tempTable))
    BEGIN
    -- Do some data seeding or whatever
    END

-- Clean up - delete the tempTable.
IF OBJECT_ID('tempdb..#tempTable') IS NOT NULL DROP TABLE #tempTable

If you're using at least SQL Server 2008, you can specify WITH VALUES at the time of column addition, which will populate existing records with the default value for that attribute.

IF COL_LENGTH('[dbo].[Trucks]', 'Is4WheelDrive') IS NULL
BEGIN

	ALTER TABLE [dbo].[Trucks]
	ADD [Is4WheelDrive] BIT NULL DEFAULT 1
	WITH VALUES;

END

This will add a new column, [Is4WheelDrive], to the table [dbo].[Trucks] if that column doesn't exist. The new column, if added, will populate existing records with the default value, which in this case is a BIT value of 1. If the column already existed, no records would be modified.