问题描述
我有一个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:
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似乎正在评估“如果”声明，即使条件不正确的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！