Rajendra Gupta
SQL Divide by Zero

Methods to avoid the SQL divide by zero error

October 21, 2019 by

This article explores the SQL divide by zero error and various methods for eliminating this.

Introduction

We all know that in math, it is not possible to divide a number by zero. It leads to infinity:

SQL Divide by Zero

Source: www.1dividedby0.com

If you try to do in calculator also, you get the error message – Cannot Divide by zero:

Divide by Zero message in calculator

We perform data calculations in SQL Server for various considerations. Suppose we perform an arithmetic division operator for calculating a ratio of products in a store. Usually, the division works fine, and we get the ratio:

SQL divide

Someday, the product2 quantity goes out of stock and that means we do not have any quantity for product2. Let’s see how the SQL Server query behaves in this case:

We get SQL divide by zero error messages (message id 8134, level 16):

SQL error message

We do not want our code to fail due to these errors. It is a best practice to write code in such a way that it does not give divide by zero message. It should have a mechanism to deal proactively with such conditions.

SQL Server provides multiple methods for avoiding this error message. Let’s explore it in the next section.

Method 1: SQL NULLIF Function

We use NULLIF function to avoid divide by zero error message.

The syntax of NULLIF function:

It accepts two arguments.

  • If both the arguments are equal, it returns a null value

    For example, let’s say both arguments value is 10:

    In the screenshot, we can see that the output is null:

    NULLIF function

  • If both the arguments are not equal, it returns the value of the first argument

    In this example, both argument values differ. It returns the output as value of first argument 10:

    NULLIF function output

Let’s modify our initial query using the SQL NULLIF statement. We place the following logic using NULLIF function for eliminating SQL divide by zero error:

  • Use NULLIF function in the denominator with second argument value zero
  • If the value of the first argument is also, zero, this function returns a null value. In SQL Server, if we divide a number with null, the output is null as well
  • If the value of the first argument is not zero, it returns the first argument value and division takes place as standard values

Execute this modified query. We can see the output NULL because denominator contains value zero.

NULLIF to avoid error message

Do we want null value in the output? Is there any method to avoid null and display a definite value?

Yes, we can use SQL ISNULL function to avoid null values in the output. This function replaces the null value in the expression1 and returns expression2 value as output.

Let’s explore the following query with a combination of SQL NULLIF and SQL ISNULL function:

  • First argument ((@Product1 / NULLIF(@Product2,0)) returns null
  • We use the ISNULL function and specify the second argument value zero. As we have the first argument null, the output of overall query is zero (second argument value)

NULLIF and ISNULL function

Method 2: Using CASE statement to avoid divide by zero error

We can use a CASE statement in SQL to return values based on specific conditions. Look at the following query. It does the following task with the Case statement.

The Case statement checks for the value of @Product2 parameter:

  • If the @Product2 value is zero, it returns null
  • If the above condition is not satisfied, it does the arithmetic operation (@Product1/@Product2) and returns the output

Using CASE statement to avoid divide by zero error

Method 3: SET ARITHABORT OFF

We can use set methods to control query behavior. By default, SQL Server has a default value of SET ARITHABORT is ON. We get SQL divide by zero error in the output using the default behavior.

The T-SQL syntax for controlling the ARITHABORT option is shown below:

  • Using ARITHABORT ON, the query will terminate with divide by zero message. It is the default behavior. For this demo, let’s enable it using the SET ARITHABORT ON statement:

    We get the SQL divide by zero error messages:

    default property ARITHABORT ON

  • Using ARITHABORT OFF, the batch will terminate and returns a null value. We need to use ARITHABORT in combination with SET ANSI_WARNINGS OFF to avoid the error message:

    ARITHABORT OFF to supress error message

    We can use the following query to check the current setting for the ARITHABORT parameter:

    The default ARITHABORT setting for SSMS is ON. We can view it using SSMS Tools properties. Navigate to Tools -> Options -> Advanced:

    SSMS tool default property

    Many client applications or drivers provide a default value of ARITHABORT is OFF. The different values might force SQL Server to produces a different execution plan, and it might create performance issues. You should also match the setting similar to a client application while troubleshooting the performance issues.

    Note: You should not modify the value of ARITHABORT unless required. It might create performance issues, as well. I would suggest using alternative methods (as described earlier) for avoiding SQL divide by zero error.

Conclusion

In this article, we explored the various methods for avoiding SQL divide by zero error. It is best practice to be proactive and use these mechanisms so that code does not fail in real-time.

Rajendra Gupta
Latest posts by Rajendra Gupta (see all)

Related posts:

  1. SQL Lag function overview and examples
  2. SQL Server Lead function overview and examples
  3. Methods to Insert Data into SQL Server
  4. SQL Injection: Introduction and prevention methods in SQL Server
  5. Read SQL Server error logs using the xp_readerrorlog command
168 Views