Recently, one of our customer came across this error as it is not possible to divide a number by zero. It leads to infinity. We perform data calculations in SQL Server for various considerations.
If you'd like to handle division by zero gracefully, you can use the NULLIF function. NULLIF takes two arguments: the expression of interest, and the value you want to override. If the first argument is equal to the second, then NULLIF returns NULL; otherwise, it returns the first argument.
Here at Ibmi Media, as part of our Server Management Services, we regularly help our Customers to resolve related SQL errors.
In this context, we shall look into methods to fix this SQL error.

What causes the error 'SQL Server divide by zero error encountered' ?

To start with, If the product2 quantity goes out of stock and that means we do not have any quantity for product2.

DECLARE @Product1 INT;

DECLARE @Product2 INT;

SET @Product1 = 50;

SET @Product2 = 0;

SELECT @Product1 / @Product2 ProductRatio;

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

Msg 8134, Level 16, State 1, Line 13

Divide by zero error encountered.

Methods to fix the error 'SQL Server divide by zero error encountered' ?

Note that 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.
Now apply the tips given below to resolve this SQL error.

1. SQL NULLIF Function

Initially, we use NULLIF function to avoid divide by zero error message.
The syntax of NULLIF function:

NULLIF(expression1, expression2)

It accepts two arguments.
i. Firstly, If both the arguments are equal, it returns a null value
For example, suppose that the value of both arguments is 10:

SELECT NULLIF(10, 10) result;

In this case, the output will be null.

ii. Secondly, 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:

SELECT NULLIF(10, 5) result;

We can modify our initial query using the SQL NULLIF statement.

We place the following logic using NULLIF function for eliminating SQL divide by zero error:
a. Use NULLIF function in the denominator with second argument value zero
b. 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.
c. If the value of the first argument is not zero, it returns the first argument value and division takes place as standard values:

DECLARE @Product1 INT;

DECLARE @Product2 INT;

SET @Product1 = 50;

SET @Product2 = 0;

SELECT @Product1 / NULLIF(@Product2,0) ProductRatio;

Execute this modified query. We will get the output as NULL because denominator contains value zero.
If we do not want null value in the output, we can use SQL ISNULL function to avoid null values in the output and display a definite value. This function replaces the null value in the expression1 and returns expression2 value as output.

2. Using CASE statement to avoid divide by zero error

 Secondly, you can use a CASE statement in SQL to return values based on specific conditions. The Case statement checks for the value of @Product2 parameter:
i. If the @Product2 value is zero, it returns null.
ii. If the above condition is not satisfied, it does the arithmetic operation (@Product1/@Product2) and returns the output:

DECLARE @Product1 INT;

DECLARE @Product2 INT;

SET @Product1 = 50;

SET @Product2 = 0;

SELECT CASE

WHEN @Product2 = 0

THEN NULL

ELSE @Product1 / @Product2

END AS ProductRatio;

We will get output as NULL.

3. SET ARITHABORT OFF

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:

SET ARITHABORT { ON | OFF }

i. Using ARITHABORT ON, the query will terminate with divide by zero message.
It is the default behavior:

SET ARITHABORT ON — Default

SET ANSI_WARNINGS ON

DECLARE @Product1 INT;

DECLARE @Product2 INT;

SET @Product1 = 50;

SET @Product2 = 0;

SELECT @Product1 / @Product2 ProductRatio;

We get the SQL divide by zero error messages.

ii. 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:

SET ARITHABORT OFF

SET ANSI_WARNINGS OFF

DECLARE @Product1 INT;

DECLARE @Product2 INT;

SET @Product1 = 50;

SET @Product2 = 0;

SELECT @Product1 / @Product2 ProductRatio;

We will get the output as NULL.

Finally, you can use the following query to check the current setting for the ARITHABORT parameter:

DECLARE @ARITHABORT VARCHAR(3) = ‘OFF’;

IF ( (64 & @@OPTIONS) = 64 ) SET @ARITHABORT = ‘ON’;

SELECT @ARITHABORT AS ARITHABORT;

The default ARITHABORT setting for SQL Server Management Studio (SSMS) is ON.

We can view it using SSMS Tools properties. Navigate to Tools -> Options -> Advanced.
We should not modify the value of ARITHABORT unless required. It might create performance issues, as well.
It is better to use other methods for avoiding SQL divide by zero error.

[Need urgent assistance in fixing SQL errors? We can help you. ]