SR0007: Use ISNULL(column, default_value) on nullable columns in expressions
RuleId |
SR0007 |
Category |
Microsoft.Performance |
Breaking Change |
Non-breaking |
Cause
An ISNULL function was not used in a comparison expression where a column could contain a NULL value.
Rule Description
If your code compares two NULL values or a NULL value with any other value, your code will return an unknown result.
How to Fix Violations
You should explicitly indicate how to handle NULL values in comparison expressions by wrapping each column that can contain a NULL value in an ISNULL function.
When to Suppress Warnings
Because the results of the comparison are indeterminate, you should not suppress this warning.
Example
This example shows a simple table definition and two stored procedures. The table contains a column, [c2], which can contain a NULL value. The first procedure, [ProcedureWithWarning], compares [c2] to a constant value. The second procedure fixes the issue by wrapping [c2] with a call to the ISNULL function.
CREATE TABLE [dbo].[Table1]
(
[ID] INT NOT NULL IDENTITY(0, 1),
[c1] INT NOT NULL PRIMARY KEY,
[c2] INT
)
ON [PRIMARY]
CREATE PROCEDURE [dbo].[ProcedureWithWarning]
AS
BEGIN
SELECT COUNT(*) FROM [dbo].[Table1]
WHERE [c2] > 2;
END
CREATE PROCEDURE [dbo].[ProcedureFixed]
AS
BEGIN
SELECT COUNT(*) FROM [dbo].[Table1]
WHERE ISNULL([c2],0) > 2;
END