SQL SERVER – How to Fix Error 8134 Divide by Zero Error Encountered

Here is one of the most popular questions: How to overcome (Error 8134) Divide by Zero Error Encountered in SQL Server?

Before we see the answer of this question, let us see how to recreate this error.

Run following script in SQL Server Management Studio window.

DECLARE @Var1 FLOAT;
DECLARE @Var2 FLOAT;
SET @Var1 = 1;
SET @Var2 = 0;
SELECT @Var1/@Var2 MyValue;

When you execute above script you will see that it will throw error 8134.

Msg 8134, Level 16, State 1, Line 6
Divide by zero error encountered.

Here is the screenshot of the error.

Now there are multiple ways to avoid this error to happen. We will see two of the most popular methods to overcome this error.

Method 1: Use NullIf Function

Here is the SQL script with NullIf Function

DECLARE @Var1 FLOAT;
DECLARE @Var2 FLOAT;
SET @Var1 = 1;
SET @Var2 = 0;
SELECT @Var1/NULLIF(@Var2,0) MyValue;

When you use the NULLIF function, it converts the zero value to Null and leading to the entire result set to be NULL instead of an error.

Method 2: Use Case Statement

Here is the SQL script with CASE Statement

DECLARE @Var1 FLOAT;
DECLARE @Var2 FLOAT;
SET @Var1 = 1;
SET @Var2 = 0;
SELECT CASE WHEN @Var2 = 0 THEN NULL
ELSE @Var1/@Var2
END MyValue;

When you use CASE statement, it converts the zero value to Null and leading to the entire result set to be NULL instead of an error.

Let me know if you have any other alternate solution. I will be happy to publish in the blog with due credit.

Reference: Pinal Dave (https://blog.sqlauthority.com)