包含在 IF EXISTS 块中的 UPDATE 语句

2021-09-10 00:00:00 sql tsql sql-server

我正在尝试编写一个更新列的 DML 脚本，但我想确保该列首先存在，因此我将其包装在 IF EXISTS 块中

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

所以奇怪的是，即使它没有满足条件，它也会尝试执行更新.所以列不存在并且 UPDATE 语句运行并且我收到一个错误.为什么?

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

UPDATE 命令是否有什么特别之处导致其行为如此?

Is there something special about an UPDATE command that causes it to behave this way?

推荐答案

问题是脚本会被编译/解析，如果该列不存在，就会出现编译/解析错误.

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

无效的列名'IsClarityEnabled'.

Invalid column name 'IsClarityEnabled'.

相关文章