Fixing Error: 8134 (Devided By Zero Error Encountered) in SQL Server
Posted by Prashant on July 2, 2010
While performing mathematical operations it throws Divided by zero error.
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).
There can be many ways to handle this error. Here are some of my workarounds in SQL Server.
- Using NULLIF & ISNULL/ COALESCE
- Using CASE
- Using ARITHABORT & ANSI_WARNINGS
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.
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.
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.