SQL error stating invalid column name when I have verification if it exists. Why?

There is staging script, which creates new column DOCUMENT_DEFINITION_ID stages it with values of MESSAGE_TYPE_ID + 5 and then removes column MESSAGE_TYPE_ID.

First time everything run ok, but when I run script second time I'm getting this error:

Invalid column name 'MESSAGE_TYPE_ID'.

It makes no sense since, I have verification if that column exists.

IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = 'MESSAGE_TYPE_ID' AND TABLE_NAME = 'DOCUMENT_QUEUE')
BEGI开发者_Go百科N
  UPDATE DOCUMENT_QUEUE SET DOCUMENT_DEFINITION_ID = MESSAGE_TYPE_ID + 5 --Error here.. but condition is not met

Why?

Try this

IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = 'MESSAGE_TYPE_ID' AND TABLE_NAME = 'DOCUMENT_QUEUE')
BEGIN
   EXEC( 'UPDATE DOCUMENT_QUEUE SET DOCUMENT_DEFINITION_ID = MESSAGE_TYPE_ID + 5 ')
END

It bypasses the delayed name resolution by wrapping the update in dynamic statement.

Delayed resolution of names only happens on tables that don't exist. You will need to drop and create the entire table.

Also, you can cheat the SQL validator by appending the next code at the begging of your script:

IF 1 = 0
    alter table DOCUMENT_QUEUE add MESSAGE_TYPE_ID int NULL;

This code will never run but the SQL validator doesn't know about it. :)