Invalid column name on sql server update after column create

Question

Does anyone see what's wrong with this code for SQL Server?

IF NOT EXISTS(SELECT *
              FROM   sys.columns
              WHERE  Name = 'OPT_LOCK'
                     AND object_ID = Object_id('REP_DSGN_SEC_GRP_LNK'))
  BEGIN
      ALTER TABLE REP_DSGN_SEC_GRP_LNK
        ADD OPT_LOCK NUMERIC(10, 0)

      UPDATE REP_DSGN_SEC_GRP_LNK
      SET    OPT_LOCK = 0

      ALTER TABLE REP_DSGN_SEC_GRP_LNK
        ALTER COLUMN OPT_LOCK NUMERIC(10, 0) NOT NULL
  END;

When I run this, I get:

Msg 207, Level 16, State 1, Line 3
Invalid column name 'OPT_LOCK'.

on the update command.

Thanks.

score 47 · Accepted Answer · edited May 23 '17 at 11:54

In this case you can avoid the problem by adding the column as NOT NULL and setting the values for existing rows in one statement as per my answer here.

More generally the problem is a parse/compile issue. SQL Server tries to compile all statements in the batch before executing any of the statements.

When a statement references a table that doesn't exist at all the statement is subject to deferred compilation. When the table already exists it throws an error if you reference a non existing column. The best way round this is to do the DDL in a different batch from the DML.

If a statement both references a non existing column in an existing table and a non existent table the error may or may not be thrown before compilation is deferred.

You can either submit it in separate batches (e.g. by using the batch separator GO in the client tools) or perform it in a child scope that is compiled separately by using EXEC or EXEC sp_executesql.

The first approach would require you to refactor your code as an IF ... cannot span batches.

IF NOT EXISTS(SELECT *
              FROM   sys.columns
              WHERE  Name = 'OPT_LOCK'
                     AND object_ID = Object_id('REP_DSGN_SEC_GRP_LNK'))
  BEGIN
      ALTER TABLE REP_DSGN_SEC_GRP_LNK
        ADD OPT_LOCK NUMERIC(10, 0)

      EXEC('UPDATE REP_DSGN_SEC_GRP_LNK SET OPT_LOCK = 0');

      ALTER TABLE REP_DSGN_SEC_GRP_LNK
        ALTER COLUMN OPT_LOCK NUMERIC(10, 0) NOT NULL
  END;

This is the approach I've been using for years, and it always amazes me that we are forced to take this awkward dynamic sql route whenever we want to add a new column and populate it with data... — Ola Berntsson, Jan 18 '18 at 10:15
I agree with Ola. The "When a statement references a table that doesn't exist at all the statement is subject to deferred compilation. When the table already exists it throws an error if you reference a non existing column." behavior makes no sense. A simple application of the parallel construction principle indicates that *both* situations should be handled with deferred compilation. — Arthur, Sep 02 '21 at 17:05

M Wat · Answer 2 · 2020-02-20T20:50:14.323

3

The root cause of the error is the newly added column name is not reflected in the sys.syscolumns and sys.columns table until you restart SQL Server Management Studio.

edited Feb 20 '20 at 20:50

answered Feb 20 '20 at 20:42

M Wat

39
4

1

Not remotely true. All restarting management studio will do for you is refresh object explorer and intellisense. This has nothing to do with query execution errors or contents of system tables and views – Martin Smith Sep 03 '21 at 07:53

score 0 · Answer 3 · edited Oct 24 '12 at 18:44

0

For your information,you can replace the IF NOT EXISTS with the COL_LENGTH function. It takes two parameters,

Table Name and
Column you are searching for

If the Column is found then it returns the range of the datatype of the column Ex: Int (4 bytes), when not found then it returns a NULL.

So, you could use this as follows and also combine 3 Statements into one.

IF (SELECT COL_LENGTH('REP_DSGN_SEC_GRP_LNK','OPT_LOCK')) IS NULL

BEGIN

    ALTER TABLE REP_DSGN_SEC_GRP_LNK
    ADD OPT_LOCK NUMERIC(10, 0) NOT NULL DEFAULT 0

END;

Makes it simpler.

edited Oct 24 '12 at 18:44

SolarBear

4,534
4
37
53

answered Sep 21 '12 at 19:12

Prakash

199
6

1

This doesn't solve the OP's problem. Adding a DEFAULT constraint is not the same as updating all values on a new column. – Neo Mar 29 '18 at 11:09

Invalid column name on sql server update after column create

3 Answers3

Linked

Related