SA0168 : Possible division by zero not handled according the practice

Introduction

Division by zero in SQL can lead to errors or unexpected results, impacting query execution and data accuracy.

Description

In SQL Server, dividing by zero results in an error that can interrupt query execution. This is especially significant in production environments where queries must run smoothly without unexpected stops. When using T-SQL, ensuring that divisions handle cases where the divisor might be zero is crucial for maintaining data integrity and consistent performance.

For example:

-- Example of problematic query that causes division by zero
SELECT @num / @num2;

In this query, if @num2 is zero, SQL Server will raise a division by zero error. This is problematic because it interrupts the query execution and might result in unprocessed data or incomplete results.

Uncaught division by zero errors can disrupt operations and lead to data loss.
Failing to handle possible NULL results can lead to unexpected behavior in analytical calculations.

How to fix

To resolve division by zero errors in SQL queries, use the NULLIF function. This approach ensures that any division operation where the divisor might be zero is safely handled, preventing query execution from being interrupted.

Follow these steps to address the issue:

1.Identify the division operation where the divisor can potentially be zero. For example, in the expression @num / @num2 .

2.Use the NULLIF function to handle the possibility of a zero divisor. Modify the expression to @num / NULLIF(@num2, 0) .

3.Wrap the entire expression with the ISNULL function to provide a default value if the division results in a NULL. For example, ISNULL(@num / NULLIF(@num2, 0), -1) , where -1 is a placeholder default value.

For example:

-- Example of corrected query to avoid division by zero
SELECT ISNULL(@num / NULLIF(@num2, 0), -1) AS Result;

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.

Additional Information

There is no additional info for this rule.

Example Test SQL

-- Test Case 1: The violation should be reported
SELECT col1/col2 FROM Table1

-- Test Case 2: The violation should not be reported only when DivisionByZeroRequiresDefaultValue parameter is 'no' or the result of the devision is handled

SELECT col1/NULLIF(col2,0.0), col1/0.0, col1/0x0, ol1/(((NULLIF(col2,0x0))))  FROM Table1

SELECT coalesce(col1/NULLIF(col2,0x0),1) FROM Table1

DECLARE @i int = 5;
DECLARE @num float = 10;

WHILE @i > -5
BEGIN

-- Test Case 3: The case is handled correctly
    SELECT ISNULL(@num / NULLIF(@i,0),@num);

-- Test Case 4: The case is handled correctly
    SELECT COALESCE(@num / NULLIF(@i,0),@num);

    SET @i = @i - 1;

-- Test Case 5: The violation should not be reported, because the rule is suppressed
    SELECT @num = @num + col1/@i  FROM Table1  /*IGNORE:SA0168*/

    SELECT 10/@num, @num/ 10, @num/(10*10) , @num/(10*@num)
END

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.	6	11
3	SA0168 : Possible division by zero not handled according the practice.	6	34
4	SA0168 : Possible division by zero not handled according the practice.	6	44
5	SA0168 : Possible division by zero not handled according the practice.	6	53
6	SA0168 : Possible division by zero not handled according the practice.	27	13
7	SA0168 : Possible division by zero not handled according the practice.	27	49

Analysis Rules