SA0168 : Possible division by zero not handled according the practice

The topic describes the SA0168 analysis rule.

Message

Possible division by zero not handled according the practice

Description

The rule reports division by zero cases which are not handled using the NULLIF function.

This function takes two parameters and if they are equal, a NULL value is returned.

Example:

SQL
Copy
1SELECT @num / @num2
2SELECT @num / NULLIF(@num2,0)

When rule’s DivisionNullResultMustBeHandled parameter is set to ‘no’, the rule also reports when the divisor wrapped in NULLIF function, but the possible NULL result is not wrapped by neither ISNULL or COALESCE functions.

SQL
Copy
1-- NULL result is not handled
2SELECT @num / NULLIF(@num2,0)
3
4-- NULL result is handled
5SELECT COALESCE(@num / NULLIF(@num2,0), -1)
6SELECT ISNULL(@num / NULLIF(@num2,0), -1)

How to fix

Handle the possible devision by zero using the NULLIF function.For example the expression `@num / num2` can be handled like this `ISNULL(@num / NULLIF(@num2,0), -1)`.

Scope

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

Parameters
Name Description Default Value
DivisionNullResultMustBeHandled

The parameter specifies if the not handled null result from the devision should be reported or not.

yes
Remarks

The rule does not need Analysis Context or SQL Connection.

Effort To Fix
8 minutes per issue.
Categories

Design Rules, Bugs

Additional Information

There is no additional info for this rule.

Example Test SQL
SQL
Copy
 1-- Test Case 1: The violation should be reported
 2SELECT col1/col2 FROM Table1
 3
 4SELECT col1/col2 FROM Table1
 5
 6-- Test Case 2: The violation should not be reported only when DivisionByZeroRequiresDefaultValue parameter is 'no' or the result of the devision is handled
 7SELECT col1/NULLIF(col2,0.0) FROM Table1
 8SELECT col1/0.0 FROM Table1
 9SELECT col1/0x0 FROM Table1
10
11SELECT col1/(((NULLIF(col2,0x0)))) FROM Table1
12SELECT coalesce(col1/NULLIF(col2,0x0),1) FROM Table1
13
14
15DECLARE @i int = 5;
16DECLARE @num float = 10;
17
18WHILE @i > -5
19BEGIN
20
21-- Test Case 3: The case is handled correctly 
22    SELECT ISNULL(@num / NULLIF(@i,0),@num);
23
24-- Test Case 4: The case is handled correctly 
25    SELECT COALESCE(@num / NULLIF(@i,0),@num);
26
27    SET @i = @i - 1;
28
29-- Test Case 5: The violation should not be reported, because the rule for the operator
30    SELECT @num = @num + col1/@i  /*IGNORE:SA0168*/ FROM Table1
31
32-- Test Case 6: The violation should not be reported, because the rule is ignored for the line 
33    SELECT @num = @num + col1/@i FROM Table1 -- IGNORE:SA0168(LINE)
34
35-- Test Case 7: The violation should not be reported, because the rule is ignored for the statement 
36    SELECT @num = @num + col1/@i
37
38    FROM Table1   -- IGNORE:SA0168(STATEMENT)
39
40    SELECT 10/@num, @num/ 10, @num/(10*10) , @num/(10*@num)
41END
Analysis Results
  Message Line Column
1 SA0168 : Possible division by zero not handled according the practice. 2 11
2 SA0168 : Possible division by zero not handled according the practice. 4 11
3 SA0168 : Possible division by zero not handled according the practice. 7 11
4 SA0168 : Possible division by zero not handled according the practice. 8 11
5 SA0168 : Possible division by zero not handled according the practice. 9 11
6 SA0168 : Possible division by zero not handled according the practice. 11 11
7 SA0168 : Possible division by zero not handled according the practice. 40 13
8 SA0168 : Possible division by zero not handled according the practice. 40 49
See Also

Other Resources

Analysis Rules