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

user_232881 0
Viewed 8234

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')
BEGIN
  UPDATE DOCUMENT_QUEUE SET DOCUMENT_DEFINITION_ID = MESSAGE_TYPE_ID + 5 --Error here.. but condition is not met

Why?

tsql sql database
3 Answers

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. :)

user_71642790
Related