我有一个桌面应用程序正在转换为web,我很难理解查询的IsNull部分的用途。这个查询是针对MsSQL的,我知道它有一个IsNull函数,但事实并非如此。所以我对它的用途感到困惑。以下是我的查询:
UPDATE tb_category
SET
Email = @Email,
CandidateID = @CandidateID,
Code = @Code,
TestDate = @TestDate,
Description = @Description,
PointsEarned = @PointsEarned,
PointsAvailable = @PointsAvailable,
Average25th = @Average25th,
Average75th = @Average75th,
ImportedDate = @ImportedDate,
CreationDate = @CreationDate,
TestNum = @TestNum,
CategoryNum = @CategoryNum
WHERE ((Email = @Original_Email)
AND (CandidateID = @Original_CandidateID)
AND (Code = @Original_Code)
AND (TestDate = @Original_TestDate)
AND ((@IsNull_Description = 1 AND Description IS NULL) OR (Description = @Original_Description))
AND (PointsEarned = @Original_PointsEarned)
AND ((@IsNull_PointsAvailable = 1 AND PointsAvailable IS NULL) OR (PointsAvailable =
@Original_PointsAvailable))
AND ((@IsNull_Average25th = 1 AND Average25th IS NULL) OR (Average25th = @Original_Average25th))
AND ((@IsNull_Average75th = 1 AND Average75th IS NULL) OR (Average75th = @Original_Average75th))
AND ((@IsNull_ImportedDate = 1 AND ImportedDate IS NULL) OR (ImportedDate = @Original_ImportedDate))
AND ((@IsNull_CreationDate = 1 AND CreationDate IS NULL) OR (CreationDate = @Original_CreationDate))
AND (TestNum = @Original_TestNum)
AND (CategoryNum = @Original_CategoryNum));
我试图通过删除IsNull部分来简化update语句,但没有成功。
在SQL中,null不等于(=(任何值,甚至不等于另一个null,因此在查询中,如果两个值都为null(旧值和新值(,则需要考虑到这一点,并使用is null检查值。
我在WHERE
子句中看到这种模式重复了好几次:
@IsNull_Description = 1 AND Description IS NULL
这意味着一个变量@IsNull_SomeColumnName
(可能在代码的早期设置(的值为1
,该变量所涉及的列当前为NULL
。
函数IsNull(Param1, Param2)
用于在第一参数IS NULL
的情况下用第二参数的值代替第一参数的值,并且函数返回Param2
的值。
在SQL Server和许多其他RDBMS中,IS NULL
语法用于检查值当前是否为NULL
。这里,如果Description
为空,Description IS NULL
将返回TRUE
,如果不是,则返回FALSE
。