T-Sql似乎正在评估“如果”声明，即使条件不正确 [英] T-Sql appears to be evaluating "If" statement even when the condition is not true

问题描述

我有一个T-Sql脚本，其中此脚本的一部分会检查a表中是否存在某个列。如果是这样，我希望它执行例程......如果没有，我希望它绕过这个例程。我的代码如下所示：

I have a T-Sql script where part of this script checks to see if a certain column exists in the a table. If so, I want it to execute a routine... if not, I want it to bypass this routine. My code looks like this:

IF COL_LENGTH('Database_Name.dbo.Table_Name', 'Column_Name1') IS NOT NULL
    BEGIN
        UPDATE Table_Name
            SET Column_Name2 = (SELECT Column_Name3 FROM Table_Name2
                WHERE Column_Name4 = 'Some Value')
            WHERE Column_Name5 IS NULL;

        UPDATE Table_Name
            SET Column_Name6 = Column_Name1
            WHERE Column_Name6 IS NULL;
    END

我的问题是，即使Column_Name1的COL_LENGTH为空（意味着它不是我仍然在IF语句中的第二个UPDATE语句中告诉我无效的列名'Column_Name1'时出错。由于某种原因，即使条件为FALSE且我不知道原因，这个IF条件仍在评估中。

My problem is that even when COL_LENGTH of Column_Name1 is null (meaning it does not exist) I am still getting an error telling me "Invalid column name 'Column_Name1'" from the 2nd UPDATE statement in the IF statement. For some reason this IF condition is still being evaluated even when the condition is FALSE and I don't know why.

推荐答案

SQL服务器解析语句并验证它，忽略任何条件。这就是为什么以下也失败了：

SQL Server parses the statement and validates it, ignoring any if conditionals. This is why the following also fails:

IF 1 = 1
BEGIN
  CREATE TABLE #foo(id INT);
END
ELSE
BEGIN
  CREATE TABLE #foo(id INT);
END

无论是点击执行还是仅解析，都会导致：

Whether you hit Execute or just Parse, this results in:

Msg 2714，Level 16，State 1

数据库中已经有一个名为'#foo'的对象。

Msg 2714, Level 16, State 1
There is already an object named '#foo' in the database.

SQL Server不知道或关心条件的哪个分支将被输入;它无论如何都会验证批处理中的所有语句。您可以执行以下操作（由于延迟名称解析）：

SQL Server doesn't know or care which branch of a conditional will be entered; it validates all of the statements in a batch anyway. You can do things like (due to deferred name resolution):

IF <something>
BEGIN
  SELECT foo FROM dbo.Table_That_Does_Not_Exist;
END

但你做不到：

IF <something>
BEGIN
  SELECT column_that_does_not_exist FROM dbo.Table_That_Does;
END

解决方法通常是使用动态SQL：

The workaround, typically, is to use dynamic SQL:

IF <something>
BEGIN
  DECLARE @sql NVARCHAR(MAX);
  SET @sql = N'SELECT column_that_does_not_exist FROM dbo.Table_That_Does;';
  EXEC sp_executesql @sql;
END

这篇关于T-Sql似乎正在评估“如果”声明，即使条件不正确的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！