Click or drag to resize

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
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
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)

Scope

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

Parameters
NameDescriptionDefault 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.

Categories

Design Rules

Additional Information

There is no additional info for this rule.

Example Test Script
SQL
 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

 MessageLineColumn
1SA0168 : Possible division by zero not handled according the practice.211
2SA0168 : Possible division by zero not handled according the practice.411
3SA0168 : Possible division by zero not handled according the practice.711
4SA0168 : Possible division by zero not handled according the practice.811
5SA0168 : Possible division by zero not handled according the practice.911
6SA0168 : Possible division by zero not handled according the practice.1111
7SA0168 : Possible division by zero not handled according the practice.4013
8SA0168 : Possible division by zero not handled according the practice.4049
See Also

Other Resources