SA0001 : Equality and inequality comparisons involving a NULL constant found. Use IS NULL or IS NOT NULL
The topic describes the SA0001 analysis rule.
Equality and inequality comparisons involving a NULL constant found. Use IS NULL or IS NOT NULL
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
The rule has a Batch scope and is applied only on the SQL script.
Rule has no parameters.
The rule does not need Analysis Context or SQL Connection.
Design Rules, Performance Rules, Bugs
There is no additional info for this rule.
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
|1||SA0001 : Equality and inequality comparisons involving a NULL constant found. Use IS NULL or IS NOT NULL.||10||22|