What is the Purpose of the IsNull in this query?

54 views Asked by At

I have a desktop application that I am converting to web and I am having trouble understanding the purpose of the IsNull parts of the query. The query is for Ms SQL and I know it has a IsNull function but this is not it. So I'm confused as to it's purpose. Below is my query:

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));

I tried simplifying the update statement by removing the IsNull sections but that did not work.

2

There are 2 answers

1
Ivan Voitovych On BEST ANSWER

In SQL null is not equal (=) to anything—not even to another null, so in your query in case if both values are null (old and new one) you need to take that into account and check values with IS NULL.

0
Eric Brandt On

I'm seeing this pattern repeated several times in the WHERE clause:

@IsNull_Description = 1 AND Description IS NULL

It means that a variable, @IsNull_SomeColumnName, which is presumably set earlier in the code, has a value of 1, and the column that the variable relates to is currently NULL.

The function IsNull(Param1, Param2) is used to substitute the value of the second parameter for the value of the first parameter if the first parameter IS NULL, and the function returns the value of Param2.

In SQL Server, and quite a few other RDBMSs, the IS NULL syntax is used to check if a value is currently NULL. Here, Description IS NULL will return TRUE if, well, Description is null, and FALSE if it is not.