SQL Server – Alter Table Statement Failed with Msg 207, Level 16, State 1

sql serversql-server-2008-r2

I wrote a script to add a column in a table and on successful execution update its values.
I know it’s just a simple script but what is wrong with the ‘IF NOT EXISTS’ statement why script between BEGIN and END is not executing and return error . I verified there is no Database level DDL trigger to stop adding column in database.

Msg 207, Level 16, State 1, Line 24
Invalid column name ColumnName.

DECLARE @CloumnAdded bit
SET @CloumnAdded = 0

IF NOT EXISTS ( SELECT  1
                FROM    Sys.Columns
                WHERE   [Object_ID] = Object_ID('DBName.dbo.TableName')
                        AND Name = 'ColumnName' ) 
    BEGIN

        ALTER TABLE TableName  ADD ColumnName varchar(120)

        SET @CloumnAdded = 1

        select @CloumnAdded, 'step 1 '
    END


IF ( @CloumnAdded = 1 ) 
    BEGIN
        select @CloumnAdded,'step 2'

        UPDATE  TableName
        SET     ColumnName = '2669'

        select @CloumnAdded,'step 3'
    END

GO

Best Answer

Your prolem occurs because of the different steps involved in query execution. First comes parsing, then binding and finally execution. In parsing the submitted text is verified as valid SQL and converted to an internal representation. Binding resolves the names from the submitted SQL to objects inside SQL Server's catalogue. Unfortuately TableName.ColumnName does not exist when binding is attempted so that part fails.

There are two things you can do*. First, split the query into two batches, one to create the column, one to update it. Only run the second if the first succeeds. Second, put the update in dynamic SQL and run it via exec sp_executesql. This effectively creates a second batch internally.

*That I can think of right now. I'm sure wiser heads could suggest others.

Related Question