2

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
4

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.

  • @@Michael, I tried with Dynamic Sql and it worked. – AA.SC Jan 14 '15 at 10:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.