Microsoft sql native client sql server divide by zero error encountered

Stuck with ‘SQL server divide by zero error encountered’? We can help you.

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.

As part of your Server Management Services, we assist our customers with several SQL queries.

Today, let us see how to fix this error.

Cause for the error ‘SQL Server divide by zero error encountered’

Let us see what could cause 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.

How to solve the error ‘SQL Server divide by zero error encountered’?

Always, 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.

Moving ahead, let us see an effective methods followed by our Support Techs employ in order to solve this error.

Method 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.

• 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.

•  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:

• Use NULLIF function in the denominator with second argument value zero
• 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.
• 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.

Method 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:

•  If the @Product2 value is zero, it returns null.
• 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.

Method 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 }

•  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.

• 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 assistance? We can help you]

Conclusion

In short, we saw how our Support Techs resolve error ‘SQL Server divide by zero error encountered’

var google_conversion_label = «owonCMyG5nEQ0aD71QM»;

Divide by zero error encountered. 1С УТ

Доброго времени суток, уважаемые Мастера.
Проблема немного не понятная. В УТ 10.3.12.3, при проведении документа «Поступление товаров и услуг» возникает ошибка:
Ошибка при выполнении обработчика — ‘ОбработкаПроведения’
по причине:
<ОбщийМодуль.ОбщегоНазначения(4191)>: Ошибка при вызове метода контекста (Выполнить)

по причине:
Ошибка выполнения запроса
по причине:
Ошибка при выполнении операции над данными:
Microsoft SQL Server Native Client 10.0: Divide by zero error encountered.
HRESULT=80040E14, SQLSrvr: SQLSTATE=22012, state=1, Severity=10, native=8134, line=1

Знаю, что программа делит на «0», но вот где . Ошибка ссылается на следующую функцию.

Функция СформироватьЗапросПоТабличнойЧасти(ДокументОбъект, ИмяТабличнойЧасти, СтруктураПолей,
СтруктураСложныхПолей = Неопределено) Экспорт

Для Каждого Реквизит Из СтруктураПолей Цикл

ТекстЗапроса = ТекстЗапроса + «,
|Док.» + Реквизит.Значение +
» КАК » + СокрЛП(Реквизит.Ключ);

Запрос = Новый Запрос;

Если ТипЗнч(СтруктураСложныхПолей) = Тип(«Структура») Тогда // Добавим к запросу конструкции.

Для Каждого Элемент Из СтруктураСложныхПолей Цикл

ТекстЗапроса = ТекстЗапроса + «,
| » + Элемент.Значение +
» КАК » + СокрЛП(Элемент.Ключ);

Если СтруктураСложныхПолей.Свойство(«Проект») Тогда

Запрос.УстановитьПараметр(«ДатаДок»,ДокументОбъект.Дата);
КонецЕсли;
КонецЕсли;

Запрос.Текст = «ВЫБРАТЬ
| Док.НомерСтроки » + ТекстЗапроса + »
| ИЗ
| Документ.» + ДокументМетаданные.Имя + «.»+ СокрЛП(ИмяТабличнойЧасти) +
» КАК Док»+ТекстСоединение+»
| ГДЕ Док.Ссылка = &ДокументСсылка»;

// Установим параметры запроса.
Запрос.УстановитьПараметр(«ДокументСсылка» , ДокументОбъект.Ссылка);

Если ДокументОбъект[ИмяТабличнойЧасти].Количество() = 0 Тогда
Запрос.Текст = СтрЗаменить(Запрос.Текст, «ГДЕ Док.Ссылка = &ДокументСсылка», «ГДЕ ЛОЖЬ»);
КонецЕсли;

Источник

SQL Server divide by zero error encountered – How to fix

by Linsa SV | Apr 20, 2021

Stuck with ‘SQL server divide by zero error encountered’? We can help you.

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.

As part of your Server Management Services, we assist our customers with several SQL queries.

Today, let us see how to fix this error.

Cause for the error ‘SQL Server divide by zero error encountered’

Let us see what could cause 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.

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

How to solve the error ‘SQL Server divide by zero error encountered’?

Always, 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.

Moving ahead, let us see an effective methods followed by our Support Techs employ in order to solve this error.

Method 1: SQL NULLIF Function

Initially, we use NULLIF function to avoid divide by zero error message.

The syntax of NULLIF function:

It accepts two arguments.

• 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.

• 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:

• Use NULLIF function in the denominator with second argument value zero
• 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.
• If the value of the first argument is not zero, it returns the first argument value and division takes place as standard values.

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.

Method 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:

• If the @Product2 value is zero, it returns null.
• If the above condition is not satisfied, it does the arithmetic operation (@Product1/@Product2) and returns the output.

We will get output as NULL.

Method 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:

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

We get the SQL divide by zero error messages.

• 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:

We will get the output as NULL.

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

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 assistance? We can help you]

Conclusion

In short, we saw how our Support Techs resolve error ‘SQL Server divide by zero error encountered’

Are you using Docker based apps?

There are proven ways to get even more out of your Docker containers! Let us help you.

Spend your time in growing business and we will take care of Docker Infrastructure for you.

Источник

Database.Guide

Beginners

Categories

• Azure SQL Edge (16)
• Database Concepts (48)
• Database Tools (70)
• DBMS (8)
• MariaDB (420)
• Microsoft Access (17)
• MongoDB (265)
• MySQL (375)
• NoSQL (7)
• Oracle (296)
• PostgreSQL (255)
• Redis (185)
• SQL (588)
• SQL Server (888)
• SQLite (235)

5 Ways to Fix the “Divide by zero error” in SQL Server (Msg 8134)

Here are five options for dealing with error Msg 8134 “Divide by zero error encountered” in SQL Server.

The Error

First, here’s an example of code that produces the error we’re talking about:

We get the error because we’re trying to divide a number by zero. Mathematically, this does not make any sense. You can’t divide a number by zero and expect a meaningful result.

To deal with this error, we need to decide what should be returned when we try to divide by zero. For example, we might want a null value to be returned. Or we might want zero to be returned. Or some other value.

Below are some options for dealing with this error.

Option 1: The NULLIF() Expression

A quick and easy way to deal with this error is to use the NULLIF() expression:

NULLIF() returns NULL if the two specified expressions are the same value. It returns the first expression if the two expressions are different. Therefore, if we use zero as the second expression, we will get a null value whenever the first expression is zero. Dividing a number by NULL results in NULL .

Actually, SQL Server already returns NULL on a divide-by-zero error, but in most cases we don’t see this, due to our ARITHABORT and ANSI_WARNINGS settings (more on this later).

Option 2: Add the ISNULL() Function

In some cases, you might prefer to return a value other than NULL .

In such cases, you can pass the previous example to the ISNULL() function:

Here I specified that zero should be returned whenever the result is NULL .

Be careful though. In some cases, returning zero might be inappropriate. For example, if you’re dealing with inventory supplies, specifying zero might imply that there are zero products, which might not be the case.

Option 3: Use a CASE Statement

Another way to do it is to use a CASE statement:

Option 4: The SET ARITHABORT Statement

The SET ARITHABORT statement ends a query when an overflow or divide-by-zero error occurs during query execution. We can use it in conjunction with SET ANSI WARNINGS to return NULL whenever the divide-by-zero error might occur:

Microsoft recommends that you always set ARITHABORT to ON in your logon sessions, and that setting it to OFF can negatively impact query optimisation, leading to performance issues.

Some clients (such as SQL Server Management Studio) set ARITHABORT to ON by default. This is why you probably don’t see the NULL value being returned when you divide by zero. You can use SET ARITHIGNORE to change this behaviour if you prefer.

Option 5: The SET ARITHIGNORE Statement

The SET ARITHIGNORE statement controls whether error messages are returned from overflow or divide-by-zero errors during a query:

Here, I set ARITHABORT and ANSI_WARNINGS to OFF so that the statement wasn’t aborted due to the error, and NULL is returned whenever there’s a divide-by-zero error.

Note that the SET ARITHIGNORE setting only controls whether an error message is returned. SQL Server returns a NULL in a calculation involving an overflow or divide-by-zero error, regardless of this setting.

In the above example we can see that when ARITHIGNORE is ON , the division by zero error is not returned. When it’s OFF , the division by zero error message is returned.

Источник

Divide by zero error encountered

In this article, we will be discussing about the “Divide by zero error encountered” error message in SQL Server.

About the “Divide by zero error encountered” error message

Well, as the error message says, whenever we have a division within a SQL query and the denominator has the value of zero (0) we get this error.

The exact error we get in SQL Server is:

Msg 8134, Level 16, State 1, Line [Here goes the line of the denominator in your query]
Divide by zero error encountered.

So, which is the best way of addressing this issue? As it is data-related we should be precautious anyway when having a division within a query and effectively control the denominator values for handling the case where a zero might be produced.

Just for reproducing the divide by zero error, consider the following example query:

Different Approaches for Handling the Issue

There are a few approaches of handling such problem. Here I present three.

Approach 1 – Using the “SET ANSI_WARNINGS OFF” Command

By using the “SET ANSI_WARNINGS OFF” command just right before the rest of the queries, it will allow your query that produces the error not to stop the execution of the rest of the queries.

Approach 2 – Using the CASE Statement

By using the CASE statement it is possible to check the denominator value for a zero and if it is so you can use 1 in order for the division not to fail.

Alternatively, you can create a custom scalar-valued function that given an input parameter, it can check for a zero and if it is encountered it can return 1 else it should return the input:

Then you can use the above function as follows:

Strengthen you SQL Server Development Skills – Enroll to our Online Course!

Check our online course titled “ Essential SQL Server Development Tips for SQL Developers ”
(special limited-time discount included in link).

Via the course, you will sharpen your SQL Server database programming skills via a large set of tips on T-SQL and database development techniques. The course, among other, features over than 30 live demonstrations!

( Lifetime Access / Live Demos / Downloadable Resources and more!)

Approach 3 – Using the NULLIF Function

Yep, by using the NULLIF function it is possible to handle the issue of a zero denominator.
But how? 🙂

The NULLIF function takes two arguments and if they have equal values it then returns a NULL.

The idea here is to compare the denominator value with a zero via NULLIF and if it returns a NULL then to handle it with the ISNULL function (by placing the number 1)!

Which of the above three approaches is the best one? Well, this is up to you 🙂

Personally I do not prefer Approach 1 as it does not solve the problem but rather “says” to SQL Server to ignore it.

So we have Approach 2 and 3 left. Approach 2 looks appealing but still I would only use it with a function.

My personal opinion is that Approach 3 is the best one; it just uses two built-in SQL Server functions and you do not need to write much additional code for handling a zero denominator!

Tip: Also, whenever you have a division in your query keep in mind that if you use only integer variables (like in this example 🙂 and the calculated denominator value is below zero it will return a zero so be careful with that as well (you can use float or decimal instead)!

Featured Online Courses:

• Introduction to Azure SQL Database for Beginners
• SQL Server 2019: What’s New – New and Enhanced Features
• SQL Server Fundamentals – SQL Database for Beginners
• Essential SQL Server Administration Tips
• Boost SQL Server Database Performance with In-Memory OLTP
• Essential SQL Server Development Tips for SQL Developers
• Working with Python on Windows and SQL Server Databases
• Introduction to Computer Programming for Beginners
• .NET Programming for Beginners – Windows Forms with C#
• Introduction to SQL Server Machine Learning Services
• Entity Framework: Getting Started – Complete Beginners Guide
• How to Import and Export Data in SQL Server Databases
• Learn How to Install and Start Using SQL Server in 30 Mins
• A Guide on How to Start and Monetize a Successful Blog

Check Some of Our Other SQL Server Articles:

Rate this article: (1 votes, average: 5.00 out of 5)

Artemakis Artemiou is a Senior SQL Server Architect, Author, a 9 Times Microsoft Data Platform MVP (2009-2018). He has over 20 years of experience in the IT industry in various roles. Artemakis is the founder of SQLNetHub and . Artemakis is the creator of the well-known software tools Snippets Generator and DBA Security Advisor. Also, he is the author of many eBooks on SQL Server. Artemakis currently serves as the President of the Cyprus .NET User Group (CDNUG) and the International .NET Association Country Leader for Cyprus (INETA). Moreover, Artemakis teaches on Udemy, you can check his courses here.

Источник

In this article, we will be discussing about the “Divide by zero error encountered” error message in SQL Server.

About the “Divide by zero error encountered” error message

Well, as the error message says, whenever we have a division within a SQL query and the denominator has the value of zero (0) we get this error.

The exact error we get in SQL Server is:

Msg 8134, Level 16, State 1, Line [Here goes the line of the denominator in your query]
Divide by zero error encountered.

So, which is the best way of addressing this issue? As it is data-related we should be precautious anyway when having a division within a query and effectively control the denominator values for handling the case where a zero might be produced.

Just for reproducing the divide by zero error, consider the following example query:

declare @denominator int
set @denominator=0

select 1/@denominator

Different Approaches for Handling the Issue

There are a few approaches of handling such problem. Here I present three.

Approach 1 – Using the “SET ANSI_WARNINGS OFF” Command

By using the “SET ANSI_WARNINGS OFF” command just right before the rest of the queries, it will allow your query that produces the error not to stop the execution of the rest of the queries.

Example:

SET ANSI_WARNINGS OFF

declare @denominator int
set @denominator=0

select 1/@denominator

.... Other queries go here

Approach 2 – Using the CASE Statement

By using the CASE statement it is possible to check the denominator value for a zero and if it is so you can use 1 in order for the division not to fail.

Example Query:

declare @denominator int
set @denominator=0

select 1/(case @denominator when 0 then 1 else @denominator end)

Alternatively, you can create a custom scalar-valued function that given an input parameter, it can check for a zero and if it is encountered it can return 1 else it should return the input:

CREATE FUNCTION check_denominator
(
-- Function parameter
@input int
)
RETURNS int
AS
BEGIN

-- Declare local variable
DECLARE @result int

-- Check for 0, if so then return 1 else return the input
SET @result =(SELECT (CASE @input when 0 then 1 else @input end))

-- Return the result
RETURN @result

END
GO

Then you can use the above function as follows:

declare @denominator int
set @denominator=0

select 1/dbo.check_denominator(@denominator)

Strengthen you SQL Server Development Skills – Enroll to our Online Course!

Check our online course titled “Essential SQL Server Development Tips for SQL Developers”
(special limited-time discount included in link).

Via the course, you will sharpen your SQL Server database programming skills via a large set of tips on T-SQL and database development techniques. The course, among other, features over than 30 live demonstrations!

Enroll from $14.99

Approach 3 – Using the NULLIF Function

Yep, by using the NULLIF function it is possible to handle the issue of a zero denominator.
But how? 🙂

The NULLIF function takes two arguments and if they have equal values it then returns a NULL.

The idea here is to compare the denominator value with a zero via NULLIF and if it returns a NULL then to handle it with the ISNULL function (by placing the number 1)!

Example:

declare @denominator int
set @denominator=0

select 1/ISNULL(NULLIF(@denominator,0),1)

Concluding Remarks

Which of the above three approaches is the best one? Well, this is up to you 🙂

Personally I do not prefer Approach 1 as it does not solve the problem but rather “says” to SQL Server to ignore it.

So we have Approach 2 and 3 left. Approach 2 looks appealing but still I would only use it with a function.

My personal opinion is that Approach 3 is the best one; it just uses two built-in SQL Server functions and you do not need to write much additional code for handling a zero denominator!

Tip: Also, whenever you have a division in your query keep in mind that if you use only integer variables (like in this example 🙂 and the calculated denominator value is below zero it will return a zero so be careful with that as well (you can use float or decimal instead)!

Featured Online Courses:

• Introduction to Azure SQL Database for Beginners
• SQL Server 2019: What’s New – New and Enhanced Features
• SQL Server Fundamentals – SQL Database for Beginners
• Essential SQL Server Administration Tips
• Boost SQL Server Database Performance with In-Memory OLTP 
• Essential SQL Server Development Tips for SQL Developers
• Working with Python on Windows and SQL Server Databases
• Introduction to Computer Programming for Beginners
• .NET Programming for Beginners – Windows Forms with C#
• Introduction to SQL Server Machine Learning Services
• Entity Framework: Getting Started – Complete Beginners Guide
• How to Import and Export Data in SQL Server Databases
• Learn How to Install and Start Using SQL Server in 30 Mins
• A Guide on How to Start and Monetize a Successful Blog

Check Some of Our Other SQL Server Articles:

• Essential SQL Sever Administration Tips
• How to Patch a Standalone SQL Server Instance
• The SQL Server Browser Service and UDP Port 1434
• The Maximum Number of Concurrent Connections Setting in SQL Server
• Top 10 SQL Server DBA Daily Tasks List
• There is no SQL Server Failover Cluster Available to Join
• Encrypting a SQL Server Database Backup
• …more

Rate this article: (1 votes, average: 5.00 out of 5)

Loading…

Reference: SQLNetHub.com (https://www.sqlnethub.com)

© SQLNetHub

Artemakis Artemiou is a Senior SQL Server Architect, Author, a 9 Times Microsoft Data Platform MVP (2009-2018). He has over 20 years of experience in the IT industry in various roles. Artemakis is the founder of SQLNetHub and {essentialDevTips.com}. Artemakis is the creator of the well-known software tools Snippets Generator and DBA Security Advisor. Also, he is the author of many eBooks on SQL Server. Artemakis currently serves as the President of the Cyprus .NET User Group (CDNUG) and the International .NET Association Country Leader for Cyprus (INETA). Moreover, Artemakis teaches on Udemy, you can check his courses here.

Views: 5,590

We use cookies on our website to give you the most relevant experience by remembering your preferences and repeat visits. By clicking “Accept All”, you consent to the use of ALL the cookies. However, you may visit «Cookie Settings» to provide a controlled consent. Read More

Here are five options for dealing with error Msg 8134 “Divide by zero error encountered” in SQL Server.

First, here’s an example of code that produces the error we’re talking about:

SELECT 1 / 0;

Result:

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

We get the error because we’re trying to divide a number by zero. Mathematically, this does not make any sense. You can’t divide a number by zero and expect a meaningful result.

To deal with this error, we need to decide what should be returned when we try to divide by zero. For example, we might want a null value to be returned. Or we might want zero to be returned. Or some other value.

Below are some options for dealing with this error.

Option 1: The NULLIF() Expression

A quick and easy way to deal with this error is to use the NULLIF() expression:

SELECT 1 / NULLIF( 0, 0 );

Result:

NULL

NULLIF() returns NULL if the two specified expressions are the same value. It returns the first expression if the two expressions are different. Therefore, if we use zero as the second expression, we will get a null value whenever the first expression is zero. Dividing a number by NULL results in NULL.

Actually, SQL Server already returns NULL on a divide-by-zero error, but in most cases we don’t see this, due to our ARITHABORT and ANSI_WARNINGS settings (more on this later).

Option 2: Add the ISNULL() Function

In some cases, you might prefer to return a value other than NULL.

In such cases, you can pass the previous example to the ISNULL() function:

SELECT ISNULL(1 / NULLIF( 0, 0 ), 0);

Result:

0

Here I specified that zero should be returned whenever the result is NULL.

Be careful though. In some cases, returning zero might be inappropriate. For example, if you’re dealing with inventory supplies, specifying zero might imply that there are zero products, which might not be the case.

Option 3: Use a CASE Statement

Another way to do it is to use a CASE statement:

DECLARE @n1 INT = 20;
DECLARE @n2 INT = 0;

SELECT CASE
    WHEN @n2 = 0
    THEN NULL
    ELSE @n1 / @n2
END

Result:

NULL

Option 4: The SET ARITHABORT Statement

The SET ARITHABORT statement ends a query when an overflow or divide-by-zero error occurs during query execution. We can use it in conjunction with SET ANSI WARNINGS to return NULL whenever the divide-by-zero error might occur:

SET ARITHABORT OFF;
SET ANSI_WARNINGS OFF;
SELECT 20 / 0;

Result:

NULL

Microsoft recommends that you always set ARITHABORT to ON in your logon sessions, and that setting it to OFF can negatively impact query optimisation, leading to performance issues.

Some clients (such as SQL Server Management Studio) set ARITHABORT to ON by default. This is why you probably don’t see the NULL value being returned when you divide by zero. You can use SET ARITHIGNORE to change this behaviour if you prefer.

Option 5: The SET ARITHIGNORE Statement

The SET ARITHIGNORE statement controls whether error messages are returned from overflow or divide-by-zero errors during a query:

SET ARITHABORT OFF;
SET ANSI_WARNINGS OFF;

SET ARITHIGNORE ON;
SELECT 1 / 0 AS Result_1;

SET ARITHIGNORE OFF;
SELECT 1 / 0 AS Result_2;

Result:

Commands completed successfully.
Commands completed successfully.
Commands completed successfully.
+------------+
| Result_1   |
|------------|
| NULL       |
+------------+
(1 row affected)
Commands completed successfully.
+------------+
| Result_2   |
|------------|
| NULL       |
+------------+
Division by zero occurred.

Here, I set ARITHABORT and ANSI_WARNINGS to OFF so that the statement wasn’t aborted due to the error, and NULL is returned whenever there’s a divide-by-zero error.

Note that the SET ARITHIGNORE setting only controls whether an error message is returned. SQL Server returns a NULL in a calculation involving an overflow or divide-by-zero error, regardless of this setting.

In the above example we can see that when ARITHIGNORE is ON, the division by zero error is not returned. When it’s OFF, the division by zero error message is returned.

   Avtokrator

03.06.13 — 12:03

   Avtokrator

4 — 03.06.13 — 12:13

   Avtokrator

6 — 03.06.13 — 12:18

Problem

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

While performing division operation in SQL Server, you may come across this error when you try to divide a number by 0.

To avoid this error, you can check and make sure that the divisor is not 0. Another option is to catch the exception in the proper way and throw the message to the front end, so as you can easily identify what went wrong and where the exception happened. Let us see both the options.

Option 1: Check for 0 and make it NULL

This is kind of suppressing the exception. Using the NULLIF function, check the divisor for 0 and change it to NULL. A number when divided by NULL will become NULL. So, there will not be any exception and the result of the division operation is NULL.

DECLARE @variable1 FLOAT,
	@variable2 FLOAT;

SELECT @variable1 = 100,
	@variable2 = 0;

SELECT @variable1 / NULLIF(@variable2, 0) Result;

Option 2: Handle the exception

This option of handling the exception will be helpful when you are performing mathematical operations in a stored procedure. When there are several mathematical operations, catching and throwing the exception outside the stored procedure will be greatly helpful to debug and figure out the problem. Let’s see an example.

Here is a stored procedure with a division operation and exception handling. This procedure has two output parameters, one to get the result and another to get the error message.

/** Create Stored procedure **/
CREATE PROCEDURE mtb_DivisionOperation
	@Value1 FLOAT,
	@Value2 FLOAT,
	@Result FLOAT OUTPUT,
	@Error NVARCHAR(MAX) OUTPUT
AS
BEGIN
	BEGIN TRY

        SET @Result = @Value1 / @Value2;

    END TRY
    BEGIN CATCH
        SET @Error = 'Error Number: ' + CAST(ERROR_NUMBER() AS VARCHAR(10)) + '; ' + Char(10) +
        'Error Severity: ' + CAST(ERROR_SEVERITY() AS VARCHAR(10)) + '; ' + Char(10) +
        'Error State: ' + CAST(ERROR_STATE() AS VARCHAR(10)) + '; ' + Char(10) +
        'Error Line: ' + CAST(ERROR_LINE() AS VARCHAR(10)) + '; ' + Char(10) +
        'Error Message: ' + ERROR_MESSAGE()
    END CATCH
END
GO

When there divisor is 0, then the @Result output parameter will be NULL and the @ErrorMsg output parameter will have the error details.

/** Execute Stored Procedure **/
DECLARE @ResultVal FLOAT
DECLARE @ErrorMsg NVARCHAR(MAX)

EXEC mtb_DivisionOperation 
	@Value1 = 100, @Value2 = 0,  
	@Result = @ResultVal OUTPUT, @Error = @ErrorMsg OUTPUT

SELECT @ResultVal AS 'Result'
SELECT @ErrorMsg AS 'Error Message'
GO

/** Results **/

Result
----------------------
NULL

(1 row affected)

Error Message
----------------------
Error Number: 8134; 
Error Severity: 16; 
Error State: 1; 
Error Line: 11; 
Error Message: Divide by zero error encountered.

(1 row affected)

When the divisor is not 0, the @Result output parameter will have a value and the @ErrorMsg output parameter will be NULL.

/** Execute Stored Procedure **/
DECLARE @ResultVal FLOAT
DECLARE @ErrorMsg NVARCHAR(MAX)

EXEC mtb_DivisionOperation 
	@Value1 = 100, @Value2 = 3,  
	@Result = @ResultVal OUTPUT, @Error = @ErrorMsg OUTPUT

SELECT @ResultVal AS 'Result'
SELECT @ErrorMsg AS 'Error Message'
GO

/** Results **/

Result
----------------------
33.3333333333333

(1 row affected)

Error Message
----------------------
NULL

(1 row affected)

Reference

• About TRY-CATCH blocks in T-SQL at Microsoft Docs.