UPDATE statement wrapped in an IF EXISTS block

Question

I'm trying to write a DML script that updates a column but I wanted to make sure the column existed first so I wrapped it in a 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 weirdness is that it tries to execute the update even if it fails the condition. So column doesn't exist and the UPDATE statement runs and I get an error. Why?

Even stranger is that this does work:

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 something special about an UPDATE command that causes 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 compile/parse error.

Invalid column name 'IsClarityEnabled'.

Answer 2

it tries to execute the update even if it fails the condition

Are you sure? I suspect that what is actually happening is that SQL Server is attempting to parse the UPDATE, whatever the value of the condition. Since parsing happens before execution, at parse time SQL Server can't 'know' that you have protected this UPDATE with a check - the parser only knows that there isn't an IsClarityEnabled column on Client, and so it complains.

The reason the EXEC works as you want is preciely because the string literal isn't processed by the parser. This is the standard way of having scripts that must run against a schema that isn't known until execution time.