CODEWITHSUNDEEP
sqlsql-servert-sql
ant
ant

Reputation: 243

UPDATE statement wrapped in an IF EXISTS block

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?

Upvotes: 5

Views: 2025

Answers (2)

AakashM
AakashM

Reputation: 63378

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.

Upvotes: 2

Adriaan Stander
Adriaan Stander

Reputation: 166506

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'.

Upvotes: 6

Related Questions