CODEWITHSUNDEEP
sql-serveralter
EasierSaidThanDone
EasierSaidThanDone

Reputation: 1897

Add column and then insert value in the same script without parser complaining

What I want to do is add a value to a column. Now it might be that the column does not yet exist.

So what I do is check if that column exists and if not I'm adding it and then insert the value.

IF COL_LENGTH('version', 'minor') = NULL
BEGIN
    ALTER TABLE version ADD minor INT null;
END
GO

UPDATE version SET minor= 4;

The problem is that the parser complains about this as the column minor does not exist at parse time.

Is there a way to make this pass in a single script?

Upvotes: 2

Views: 2581

Answers (1)

Raj
Raj

Reputation: 10853

Use either:

SET ANSI_NULLS OFF
GO 
IF COL_LENGTH('version', 'minor') = NULL
BEGIN
    ALTER TABLE [version] ADD minor INT null;
END
GO

UPDATE [version] SET minor= 4;

OR

IF COL_LENGTH('version', 'minor') IS NULL
    BEGIN
        ALTER TABLE [version] ADD minor INT null;
    END
    GO

    UPDATE [version] SET minor= 4;

You are trying to compare NULL = NULL, with ANSI_NULLS ON

Raj

Upvotes: 6

Related Questions