UPDATE statement enclosed in an IF EXISTS block

Question

I am trying to write a DML script that updates a column, but I wanted to make sure the column was the first, so I wrapped it in an IF EXISTS block

IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='Client' AND COLUMN_NAME='IsClarityEnabled') BEGIN UPDATE Client SET IsClarityEnabled = 1 WHERE ClientID = 21 END 

So, the oddity is that it tries to perform the update, even if it does not fulfill the condition. So the column does not exist and the UPDATE statement works, and I get an error. Why?

Even a stranger is that this works:

 IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='Client' AND COLUMN_NAME='IsClarityEnabled') BEGIN EXEC('UPDATE Client SET IsClarityEnabled = 1 WHERE ClientID = 21') END 

Is there anything special about the UPDATE command that forces it to behave this way?

Answer 1

The problem is that the script will be compiled / parsed, and if the column does not exist, you will have a compilation / parse error.

Invalid column name 'IsClarityEnabled'.

Answer 2

he tries to update, even if he did not fulfill the condition

Are you sure? I suspect that what is actually happening is that SQL Server is trying to parse UPDATE , regardless of the value of the condition. Because parsing takes place before execution, during parsing, SQL Server cannot "know" that you protected this UPDATE by checking - the parser knows that the Client does not have an IsClarityEnabled column, and therefore it complains.

The reason EXEC works the way you want is reliable, since the string literal is not processed by the parser. This is the standard way to have scripts that must be run against a circuit that is unknown before runtime.