Export (0) Print
Expand All

SR0007: Use ISNULL(column, default_value) on nullable columns in expressions

RuleId

SR0007

Category

Microsoft.Performance

Breaking Change

Non-breaking

An ISNULL function was not used in a comparison expression where a column could contain a NULL value.

If your code compares two NULL values or a NULL value with any other value, your code will return an unknown result.

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.

Because the results of the comparison are indeterminate, you should not suppress this warning.

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

Community Additions

ADD
Show:
© 2014 Microsoft