SQL Journey

Journey of SQL Server & Microsoft Business Intelligence

Fixing Error: 8134 (Devided By Zero Error Encountered) in SQL Server

Posted by Prashant on July 2, 2010


Problem:

While performing mathematical operations it throws Divided by zero error.

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

 

This situation often arises in production databases if the script has not been tested with sufficient data before putting the script to production database. This happens when a number is divided by 0 (zero).

Solution:

There can be many ways to handle this error. Here are some of my workarounds in SQL Server.

  1. Using NULLIF & ISNULL/ COALESCE
  2. Using CASE
  3. Using ARITHABORT & ANSI_WARNINGS

Method: 1

SELECT ISNULL(Number1 / NULLIF(Number2, 0), 0) AS [Result]
FROM tbl_err_8134

In this method uses NULLIF. In this case when the divisor is 0 (Zero) it will return NULL to the divisor, so the result will also became NULL. Then by IFNULL it returns 0 as the result is NULL here.

Method: 2

SELECT CASE WHEN Number2 = 0 THEN 0 ELSE Number1 / Number2 END AS [Result]
FROM tbl_err_8134

In this method uses CASE. Here when the divisor is 0 (Zero) it will return 0 as result or else the result will be division of two numbers.

Method: 3

SET ARITHABORT OFF
SET ANSI_WARNINGS OFF
GO

SELECT ISNULL(Number1 / Number2, 0) AS [Result]
from tbl_err_8134

Here when ARITHABORT & ANSI_WARNINGS are set to OFF it will continue processing and will return NULL as a result. To know more about ARITHABORT you can follow this link.

Download the complete script file here.

6 Responses to “Fixing Error: 8134 (Devided By Zero Error Encountered) in SQL Server”

  1. Arthur said

    Arthur…

    […]Fixing Error: 8134 (Devided By Zero Error Encountered) in SQL Server « SQL Journey[…]…

  2. Ok THIS SET ARITHABORT OFF
    SET ANSI_WARNINGS OFF can use in Views???

    • Please someone help me .It is very Urgent.

      • Prashant said

        Create the view like:

        CREATE VIEW vwTest
        AS
        SELECT Number1,
        Number2,
        ISNULL(Number1 / Number2, 0) AS [Result]
        FROM tbl_err_8134
        GO

        However while querying the view use the SET options, like:

        SET ARITHABORT OFF
        SET ANSI_WARNINGS OFF
        GO

        SELECT Number1, Number2, Result
        FROM vwTest
        GO

      • Prashant all ready I am working with dataware housing which is online server i do not want this SET ARTHABORT OFF
        SET ANSI_WARNININGS OFF can use in whole database .Already I had use ISNULL Is there any alternate solution

  3. […] Fixing Error: 8134 (Devided By Zero Error Encountered) … – Jul 02, 2010 · Problem: While performing mathematical operations it throws Divided by zero error. Msg 8134, Level 16, State 1, Line 5 Divide by zero error encountered…. […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: