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.

Answer 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;