Another tip from the great book by David Sceppa is how to handle null column values:
Suppose you have a column in your table in the Database, and that column can have NULL values, the column is named ColA. Now you wanted to updated a record by checking if ColA is NULL in the WHERE clause to allow updating:
UPDATE ..... WHERE ColA = ?
The above won't work if the value of ColA was NULL and the value of the parameter is NULL, instead you should use:
"ColA IS NULL"
However, to write a SQL Stored Procedure that allows columns with allowed NULL values to accept values either NULL or something else, you should change the above statement to something as:
UPDATE ... WHERE (ColA = ? OR ((ColA IS NULL) AND (? IS NULL)))
This means, we want the above statement to be true when ColA equals the value of "?" or when ColA is NULL and the ? is also NULL. Why is that?
Suppose you want to update a record where ColA == 5, therefore, the above statement will be true, the query will search for the record whose ColA value is 5. This is fine!
Now suppose, you want to update a record where ColA is null, so you provide NULL for the value of ?, then the query will search for the record whose ColA is null, the first part of the WHERE will fail, but the second part will succeed, since we are ORing, then the conditions will pass!
Suppose, you want to update a record whose ColA == 6, and it happens that a record in the table has the value of ColA as NULL, and then you execute the query, it will also succeed in not updating the record whose ColA is null, because we have added the second ANDing condition, (? IS NULL). So, if you wanted to update a record whose ColA value is 6, and while running the query you passed through a record whose ColA is NULL, it won't be updated, because we have to check too, if the ? is NULL, if not NULL, then the update will not work, and hence the query works great, because in the second condition, only the first left side wins, but the second right side it fails, because the value im searching for which is 6 is not NULL.
Hope this helps!
Regards
Tags: ASP.NET 2.0 - General, Databases & SQL