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

In SQL, performing division operations can sometimes lead to errors, particularly when the divisor is zero. In this article, We will learn about How to Avoid the "Divide by Zero" Error in SQL by understanding various methods with the help of examples and so on.

How to Avoid the Divide by ZeroError in SQL?

In SQL, performing division operations can sometimes lead to errors, particularly when a divisor is zero. This error, often referred to as the “divide by zero” error can disrupt your query execution and lead to inaccurate results.

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

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

Let's setup an environment:

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;

1. Using NULLIF() function

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:

2. Using CASE statement

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:

3. Using SET ARITHABORT OFF

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:

Conclusion

Handling the "divide by zero" error in SQL is crucial to ensure smooth and accurate query execution. By using techniques such as the NULLIF() function, the CASE statement, and the SET ARITHABORT OFF command, you can prevent this error from disrupting your operations. Each of these methods offers a different approach, allowing you to choose the one that best fits your needs.

snehalchitnis1851

Follow

Improve

Article Tags :

• SQL
• Databases
• SQL-Server