Click or drag to resize

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.

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
NameDescriptionDefault Value
RuleSuppressMark

Suppress rule reporting a rule violation in case a comment containing the value of this parameter is found close to the source of violation.

IGNORE:SA0122

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.

Categories

Design Rules

Additional Information
Example Test Script
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

 MessageLineColumn
1SA0122 : Column [Production].[Product].[Size] is nullable. Use ISNULL(Column,Default Value) on nullable columns in expressions.76
2SA0122 : Column [Production].[Product].[Weight] is nullable. Use ISNULL(Column,Default Value) on nullable columns in expressions.413
3SA0122 : Column [Production].[Product].[Color] is nullable. Use ISNULL(Column,Default Value) on nullable columns in expressions.421
4SA0122 : Column [Production].[Product].[Size] is nullable. Use ISNULL(Column,Default Value) on nullable columns in expressions.428
See Also

Other Resources