SA0122 : Use ISNULL(Column,Default value) on nullable columns in expressions

The topic describes the SA0122 analysis rule.

Message

Use ISNULL(Column,Default value) on nullable columns in expressions

Description

The rule checks for nullable columns used in expression without IS NULL check and not wrapped in ISNULL function call.

How to fix

If the data type is null in the table or code, then when you do comparisons or expressions then make sure to wrap in ISNULL to make sure the values are compared properly. ANSI NULL-s do not equal each other.

Scope

The rule has a Batch scope and is applied only on the SQL script.

Parameters
Name Description Default Value
IgnoreNullableColumnToNullableColumnAssignment

Ignore assignment of nullable column to nullable column.

yes
IgnoreNullableColumnInOuterJoin

Ignore nullable columns referenced inside OUTER JOIN ON clause.

yes
IgnoreNullableColumnComparedToConstant

Ignore nullable columns when compared to constant value.

yes
IgnoreNullableForeignKeyColumnComparedToReferencedKeyInJoinClause

Ignore nullabe columns which are FK columns and are compared to the FK referenced key columns in JOIN clause.

yes
Remarks

The rule requires SQL Connection. If there is no connection provided, the rule will be skipped during analysis.

Effort To Fix
5 minutes per issue.
Categories

Design Rules, Bugs

Additional Information
Example Test SQL
SQL
Copy
1CREATE PROCEDURE testsp_SA0122
2@Size varchar(10)
3AS
4SELECT Name, Weight, Color, Size
5FROM Production.Product
6WHERE Color = 'Black' AND 
7      Size = @Size
8ORDER BY Name;
Analysis Results
  Message Line Column
1 SA0122 : Column [Production].[Product].[Size] is nullable. Use ISNULL(Column,Default Value) on nullable columns in expressions. 7 6
2 SA0122 : Column [Production].[Product].[Weight] is nullable. Use ISNULL(Column,Default Value) on nullable columns in expressions. 4 13
3 SA0122 : Column [Production].[Product].[Color] is nullable. Use ISNULL(Column,Default Value) on nullable columns in expressions. 4 21
4 SA0122 : Column [Production].[Product].[Size] is nullable. Use ISNULL(Column,Default Value) on nullable columns in expressions. 4 28
See Also

Other Resources

Analysis Rules