UPDATE statement wrapped in an IF EXISTS block

Posted by formica on Stack Overflow See other posts from Stack Overflow or by formica
Published on 2010-04-06T17:03:31Z Indexed on 2010/04/06 17:13 UTC
Read the original article Hit count: 108

Filed under:
|
|

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?

© Stack Overflow or respective owner

Related posts about t-sql

Related posts about sql