How to Avoid the “divide by Zero” Error in SQL?

If we divide any number by zero, SQL gives a “divide by zero” error, as dividing by zero is undefined in mathematics.

To avoid the divide by zero in SQL use these methods:

• NULLIF() function
• CASE statement
• SET ARITHABORT OFF

Demo Database

First, we will create a demo database, declare variables, and see how to counter SQL’s “divide by zero” error message.

Query:

CREATE DATABASE Test;

DECLARE @Num1 INT;
DECLARE @Num2 INT;

SET @Num1=12;
SET @Num2=0;

Let’s discuss the three methods on how to avoid divide by zero error with examples.

Using NULLIF() function to avoid divide by zero error

If both arguments are equal, NULLIF() function returns NULL. If both arguments are not equal, it returns the value of the first argument.

Syntax:

NULLIF(exp1, exp2);

Now we are using the NULLIF() function in the denominator with the second argument value zero. 

SELECT @Num1/NULLIF(@Num2,0) AS Division;

• In the SQL server, if we divide any number with a NULL value its output will be NULL.
• If the first argument is zero, it means if the Num2 value is zero, then NULLIF() function returns the NULL value.
• If the first argument is not zero, then NULLIF() function returns the value of that argument. And the division takes place as regular.

Output:

Using the CASE statement to avoid “divide by zero” error

The SQL CASE statement is used to check the condition and return a value. It checks the conditions until it is true and if no conditions are true it returns the value in the else part.

We have to check the value of the denominator i.e the value of the Num2 variable. If it is zero then return NULL otherwise return the regular division.

SELECT CASE
WHEN @Num2=0
THEN NULL
ELSE @Num1/@Num2
END AS Division;

Output:

SET ARITHABORT OFF to avoid “divide by zero” error

To control the behavior of queries, we can use SET methods. By default, ARITHABORT is set as ON. It terminates the query and returns an error message. If we set it OFF it will terminate and returns a NULL value.

Like ARITHBORT, we have to set ANSI_WARNINGS OFF to avoid the error message.

SET ARITHABORT OFF;
SET ANSI_WARNINGS OFF;
SELECT @num1/@Num2;

Output: