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.

The best method to guard against a divide by zero error is by using the NULLIF function.

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

Example:

SELECT @num / @num2

SELECT @num / NULLIF(@num2,0)

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, New Rules

Additional Information

There is no additional info for this rule.

Example Test Script
 1-- Test Case 1: The violation should be reported
 2SELECT col1/col2 FROM Table1
 3
 4-- Test Case 2: The violation should not be reported only when DivisionByZeroRequiresDefaultValue parameter is 'yes'
 5SELECT col1/NULLIF(col2,0) FROM Table1
 6
 7DECLARE @i int = 5;
 8DECLARE @num float = 10;
 9
10WHILE @i > -5
11BEGIN
12
13-- Test Case 3: The case is handled correctly 
14    SELECT ISNULL(@num / NULLIF(@i,0),@num);
15
16-- Test Case 4: The case is handled correctly 
17    SELECT COALESCE(@num / NULLIF(@i,0),@num);
18
19    SET @i = @i - 1;
20
21-- Test Case 5: The violation should not be reported, because the rule for the operator
22    SELECT @num = @num + col1/@i  /*IGNORE:SA0168*/ FROM Table1
23
24-- Test Case 6: The violation should not be reported, because the rule is ignored for the line 
25    SELECT @num = @num + col1/@i FROM Table1 -- IGNORE:SA0168(LINE)
26
27-- Test Case 7: The violation should not be reported, because the rule is ignored for the statement 
28    SELECT @num = @num + col1/@i 
29    FROM Table1   -- IGNORE:SA0168(STATEMENT)
30
31END

Analysis Results

 MessageLineColumn
1SA0168 : Possible division by zero not handled according the practice.211
2SA0168 : Possible division by zero not handled according the practice.511
See Also

Other Resources