Share via


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

See Also

Concepts

Analyzing Database Code to Improve Code Quality