Invalid column name on sql server update after col

问题:

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.

回答1:

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; 

回答2:

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

1. Table Name and

2. 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.