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?

SQL SERVER - How to Fix Error 8134 Divide by Zero Error Encountered devidebyzero-800x600

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.

Solarwinds

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

Here is the screenshot of the error.

SQL SERVER - How to Fix Error 8134 Divide by Zero Error Encountered error8134-0

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.

SQL SERVER - How to Fix Error 8134 Divide by Zero Error Encountered error8134-1

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.

SQL SERVER - How to Fix Error 8134 Divide by Zero Error Encountered error8134-2

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)

Solarwinds
,
Previous Post
PowerShell – SQL Server Paging of Memory Identification
Next Post
SQL SERVER – SSMS Enhancement – Databases Node Filter Capability

Related Posts

9 Comments. Leave new

  • Hi,

    Please check the below code to avoid 8134 error.

    DECLARE @Var1 FLOAT;
    DECLARE @Var2 FLOAT;
    SET @Var1 = 1;
    SET @Var2 = ”; –0, 1, NULL,”
    IF(@Var2=0)
    SELECT NULL;
    ELSE
    SELECT @Var1/@Var2;

    Regards,
    SubbaReddy AV

    Reply
  • begin try
    select @var1/@var2
    end try
    begin catch
    if error_number() = 8134 select null
    else select error_number()
    end catch

    Reply
  • A solution

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

    Reply
  • Nate Hughes (@nate_hughes)
    August 29, 2016 6:59 pm

    Disclaimer: I don’t recommend this, I’d rather see it handled via code, but it works.

    SET ARITHABORT OFF;
    SET ANSI_WARNINGS OFF;

    DECLARE @Var1 FLOAT = 1, @Var2 FLOAT = 0;

    SELECT @Var1 / @Var2;

    Reply
  • DECLARE @Var1 FLOAT;
    DECLARE @Var2 FLOAT;
    SET @Var1 = 1;
    SET @Var2 = 0;
    SELECT @Var1/IIF(@Var20,@Var2,Null)MyValue;

    Reply
  • gvreddy04Vikram
    June 11, 2019 8:25 pm

    I don’t think returning NULL will solve the problem. For example what will happen if we get divided by zero exception in aggregate function?

    Reply

Leave a Reply

Menu