Click or drag to resize

SA0001 : Equality and inequality comparisons involving a NULL constant found. Use IS NULL or IS NOT NULL

The topic describes the SA0001 analysis rule.

Message

Equality and inequality comparisons involving a NULL constant found. Use IS NULL or IS NOT NULL

Description

This rule scans stored procedures, views, functions and triggers to flag use of equality and inequality comparisons involving a NULL constant. These comparisons are undefined when ANSI_NULLS option is set to ON.

It is recommended to set ANSI_NULLS to ON and use the IS keyword to compare against NULL constants.

Care must be taken when comparing null values. The behavior of the comparison depends on the setting of the SET ANSI_NULLS option.

When SET ANSI_NULLS is ON, a comparison in which one or more of the expressions is NULL does not yield either TRUE or FALSE; it yields UNKNOWN. This is because a value that is unknown cannot be compared logically against any other value. This occurs if either an expression is compared to the literal NULL, or if two expressions are compared and one of them evaluates to NULL.

For example, the following comparison always yields UNKNOWN when ANSI_NULLS is ON:

ytd_sales > NULL

The following comparison also yields UNKNOWN any time the variable contains the value NULL:

ytd_sales > @MyVariable

Scope

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

Parameters

Rule has no parameters.

Remarks

The rule does not need Analysis Context or SQL Connection.

Categories

Design Rules, Performance Rules

Additional Information

There is no additional info for this rule.

Example Test Script
SQL
 1CREATE PROCEDURE [dbo].[CustOrdersOrders]
 2 @CustomerID nchar(5)
 3AS 
 4SELECT     OrderID,
 5           OrderDate,
 6           RequiredDate,
 7           ShippedDate
 8FROM       Orders
 9WHERE      CustomerID=@CustomerID
10       AND ShippedDate!=NULL
11       AND RequiredDate IS NULL
12
13SELECT     OrderID,
14           OrderDate,
15           RequiredDate,
16           ShippedDate
17FROM       Orders  -- IGNORE:SA0001(STATEMENT)
18
19SELECT     OrderID,
20           OrderDate,
21           RequiredDate,
22           ShippedDate
23FROM       Orders
24WHERE      CustomerID=@CustomerID
25       AND ShippedDate!=NULL AND RequiredDate IS NULL -- IGNORE:SA0001(LINE)
26
27SELECT     OrderID,
28           OrderDate,
29           RequiredDate,
30           ShippedDate
31FROM       Orders
32WHERE      CustomerID=@CustomerID
33       AND ShippedDate!=NULL -- IGNORE:SA0001
34       AND RequiredDate IS NULL

Analysis Results

 MessageLineColumn
1SA0001 : Equality and inequality comparisons involving a NULL constant found. Use IS NULL or IS NOT NULL.1022
See Also

Other Resources