Divide by zero error encountered the statement has been terminated

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.

Источник

SQLShack

Methods to avoid the SQL divide by zero error

This article explores the SQL divide by zero error and various methods for eliminating this.

Introduction

We all know that in math, it is not possible to divide a number by zero. It leads to infinity:

If you try to do in calculator also, you get the error message – Cannot Divide by zero:

We perform data calculations in SQL Server for various considerations. Suppose we perform an arithmetic division operator for calculating a ratio of products in a store. Usually, the division works fine, and we get the ratio:

Someday, the product2 quantity goes out of stock and that means we do not have any quantity for product2. Let’s see how the SQL Server query behaves in this case:

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

We do not want our code to fail due to these errors. 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.

SQL Server provides multiple methods for avoiding this error message. Let’s explore it in the next section.

Method 1: SQL NULLIF Function

We use NULLIF function to avoid divide by zero error message.

The syntax of NULLIF function:

It accepts two arguments.

If both the arguments are equal, it returns a null value

For example, let’s say both arguments value is 10:

In the screenshot, we can see that the output is null:

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:

Let’s 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 can see the output NULL because denominator contains value zero.

Do we want null value in the output? Is there any method to avoid null and display a definite value?

Yes, we can use SQL ISNULL function to avoid null values in the output. This function replaces the null value in the expression1 and returns expression2 value as output.

Let’s explore the following query with a combination of SQL NULLIF and SQL ISNULL function:

• First argument ((@Product1 / NULLIF(@Product2,0)) returns null
• We use the ISNULL function and specify the second argument value zero. As we have the first argument null, the output of overall query is zero (second argument value)

Method 2: Using CASE statement to avoid divide by zero error

We can use a CASE statement in SQL to return values based on specific conditions. Look at the following query. It does the following task with the Case statement.

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

Method 3: SET ARITHABORT OFF

We can use set methods to control query behavior. 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. For this demo, let’s enable it using the SET ARITHABORT ON statement:

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 can use the following query to check the current setting for the ARITHABORT parameter:

The default ARITHABORT setting for SSMS is ON. We can view it using SSMS Tools properties. Navigate to Tools -> Options -> Advanced:

Many client applications or drivers provide a default value of ARITHABORT is OFF. The different values might force SQL Server to produces a different execution plan, and it might create performance issues. You should also match the setting similar to a client application while troubleshooting the performance issues.

Note: You should not modify the value of ARITHABORT unless required. It might create performance issues, as well. I would suggest using alternative methods (as described earlier) for avoiding SQL divide by zero error.

Conclusion

In this article, we explored the various methods for avoiding SQL divide by zero error. It is best practice to be proactive and use these mechanisms so that code does not fail in real-time.

Hi! I am Rajendra Gupta, Database Specialist and Architect, helping organizations implement Microsoft SQL Server, Azure, Couchbase, AWS solutions fast and efficiently, fix related issues, and Performance Tuning with over 14 years of experience.

I am the author of the book «DP-300 Administering Relational Database on Microsoft Azure». I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.

I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups.

Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.

Источник

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.

EDIT:
I’m getting a lot of downvotes on this recently…so I thought I’d just add a note that this answer was written before the question underwent it’s most recent edit, where returning null was highlighted as an option…which seems very acceptable. Some of my answer was addressed to concerns like that of Edwardo, in the comments, who seemed to be advocating returning a 0. This is the case I was railing against.

ANSWER:
I think there’s an underlying issue here, which is that division by 0 is not legal. It’s an indication that something is fundementally wrong. If you’re dividing by zero, you’re trying to do something that doesn’t make sense mathematically, so no numeric answer you can get will be valid. (Use of null in this case is reasonable, as it is not a value that will be used in later mathematical calculations).

So Edwardo asks in the comments «what if the user puts in a 0?», and he advocates that it should be okay to get a 0 in return. If the user puts zero in the amount, and you want 0 returned when they do that, then you should put in code at the business rules level to catch that value and return 0…not have some special case where division by 0 = 0.

That’s a subtle difference, but it’s important…because the next time someone calls your function and expects it to do the right thing, and it does something funky that isn’t mathematically correct, but just handles the particular edge case it’s got a good chance of biting someone later. You’re not really dividing by 0…you’re just returning an bad answer to a bad question.

Imagine I’m coding something, and I screw it up. I should be reading in a radiation measurement scaling value, but in a strange edge case I didn’t anticipate, I read in 0. I then drop my value into your function…you return me a 0! Hurray, no radiation! Except it’s really there and it’s just that I was passing in a bad value…but I have no idea. I want division to throw the error because it’s the flag that something is wrong.

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

The TRY...CATCH block is causing the execution of the stored procedure to terminate before it has a chance to return the values in #TEMP.

Try this stored proc:

IF OBJECT_ID('dbo.SomeProc') IS NOT NULL
DROP PROCEDURE dbo.SomeProc;
GO
CREATE PROCEDURE [dbo].[SomeProc]
AS 
BEGIN
    CREATE TABLE #TEMP
    (
        ID INT NOT NULL
    );

    INSERT INTO #TEMP 
    VALUES (1);

    INSERT INTO #TEMP 
    VALUES (1/0);

    PRINT (N'Run the SELECT');

    SELECT ID 
    FROM #TEMP;
END
GO

Running it, like this, without a TRY...CATCH, allows all the statements in the proc to run:

EXEC dbo.SomeProc;

The output:

Msg 8134, Level 16, State 1, Procedure SomeProc, Line 12
Divide by zero error encountered.
The statement has been terminated.
Run the SELECT

However, if you run it inside a TRY...CATCH:

BEGIN TRY
    EXEC dbo.SomeProc;
END TRY
BEGIN CATCH
    PRINT (ERROR_MESSAGE());
END CATCH

You see only the error message:

Divide by zero error encountered.

The PRINT (N'Run the SELECT'); never runs, and indeed the SELECT ID FROM #TEMP; never runs either. Hence no rows are returned, and nothing can be inserted into your #TEMPTABLE

From the MSDN documentation on TRY…CATCH:

If there is an error in the code that is enclosed in a TRY block, control passes to the first statement in the associated CATCH block.

As always, the devil is in the details. TRY...CATCH always aborts the code inside the BEGIN TRY...END TRY code block if any error over severity 10 occurs that does not close the database connection. Execution is immediately passed into the BEGIN CATCH...END CATCH block, even if this means aborting code in a stored proc.

Be aware that if an error occurs on any row the entire insert will not happen. The only reason you’re seeing this error, and a row being inserted, is because you have two insert statements, one that runs to completion, and one that throws an error. Take for instance:

IF OBJECT_ID('dbo.SomeProc') IS NOT NULL
DROP PROCEDURE dbo.SomeProc;
GO
CREATE PROCEDURE [dbo].[SomeProc]
AS 
BEGIN
    CREATE TABLE #TEMP
    (
        ID INT NOT NULL
    );

    INSERT INTO #TEMP 
    VALUES (1);

    INSERT INTO #TEMP 
    VALUES (1)
        , (2)
        , (3)
        , (4)
        , (1/0);

    PRINT (N'Run the SELECT');

    SELECT ID 
    FROM #TEMP;
END
GO

Using this stored proc, either inside a TRY...CATCH or with no TRY...CATCH block, will only result in a single row being inserted into the #TEMPTABLE — none of the rows in the 2nd insert will be present in the output.

• Hi All,

  Anyone know how to fix the error «Divide by zero error encountered. [SQLSTATE 22012] (Error 8134) «

  Thanks in advance

  -snt

• Derrick Leggett

  SSCrazy

  Points: 2778

  Don’t divide by zero.    You need to figure out where you are doing this and fix it. 

  Derrick Leggett
  Mean Old DBA
  When life gives you a lemon, fire the DBA.

• chris webster

  SSCommitted

  Points: 1974

  Prior to any division check the divisor for its value, if zero take a different path in code.

  Cheers

• Johan Bijnens

  SSC Guru

  Points: 135009

  Like Derrick Leggett mentioned, figure it out !

  If your investigation points the symantics are OK, then you might use a case statement .

  declare @col1  integer

  declare @col2  integer

  declare @col3  integer

  select @col1 = 8, @col2 = 0, @col3 = 2

  select  @col1 / case when @col2 = 0 then 1 else @col2 end as first_division

  ,   @col1 / case when @col3 = 0 then 1 else @col3 end as second_division

• Jonathan Stokes

  SSCrazy Eights

  Points: 9861

  You can do the same thing on the application as well if you are using one. In crystal reports and business objects I never create variables using division without setting the value to 1 in the case of zero or null values.

  You can use if then else or isnull to achieve this.

  ---

  ——————————
  The Users are always right — when I’m not wrong!

• barsuk

  SSChampion

  Points: 12280

  You might want to toggle SET ARITHABORT ON|OFF and SET ARITHIGNORE ON|OFF. Check BOL for more details.

• John Dann

  SSC Journeyman

  Points: 83

  February 3, 2006 at 8:38 am

  #618701

  I’m getting the same message.  I’m running a single step agent job executing a single proc, and the agent returns this detail:

  Divide by zero error encountered. [SQLSTATE 22012] (Error 8134)  The statement has been terminated. [SQLSTATE 01000] (Error 3621).  The step failed.

  Within the proc that is being run, I log every command called within the proc except the actual calls to the log (over 300 commands), and the log table shows that the proc completed successfully.  I’m thinking this is an agent error, but I do not know.  Where should I look at this point?  Thanks.  John

• Jo Pattyn

  SSC-Dedicated

  Points: 31811

  February 3, 2006 at 10:49 am

  #618742

  like barsuk wrote

  What are you current connection settings of

  SET ARITHABORT ON|OFF (abort batch on error)

  and SET ARITHIGNORE ON|OFF. (issue a warning but continue)

• Phil Henry

  Mr or Mrs. 500

  Points: 528

  Im running a Crystal Report from a SQL Procedure script in SQL 2008

  Ive just exoerienced this same error message and solved it by inserting WHEN 0 THEN 0 to the beginning of my script ( first few lines shown here)

  PROCEDURE [xxdbo].[CH_OperationAnalysis] @FromDate DATETIME, @ToDate DATETIME AS

  SELECT

  [tse].[Operation],

  [tse].[OperationDesc],

  [tse].[JobNo],

  CASE [tse].[OperationLength]

  WHEN 0 THEN 0

  ELSE

  ( etc etc etc

  Hope this helps

• Edward Boyle-478467

  Ten Centuries

  Points: 1134

  I always use select a/nullif(b,0) for division when there is a possibility that the divisor is zero.

• PhilPacha

  Hall of Fame

  Points: 3645

  The solution Edward Boyle posted is the one I use. If you need a non-null final result, simply add an encompassing COALESCE or ISNULL to provide the appropriate ‘default’ value.

  ISNULL(col1 / NULLIF(col2, 0), 0) will provide a final result of zero.

• nigel.

  SSChampion

  Points: 11636

  Guys you’re posting answers to a question asked in 2004. I hope they’ve fixed the problem by now. :w00t:

• Phil Henry

  Mr or Mrs. 500

  Points: 528

• priterande

  Valued Member

  Points: 63

  select COALESCE(MIN(Act) / NULLIF(MAX(Act),0), 0) as minvsmaxAct from table_name

• Luis Cazares

  SSC Guru

  Points: 183694

  priterande (11/5/2015)

  ---

  select COALESCE(MIN(Act) / NULLIF(MAX(Act),0), 0) as minvsmaxAct from table_name

  This is what I usually do, except that I don’t reply to 5 year old threads. :w00t:

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

In this article, we will look at how to avoid the “divide by zero” error in SQL. If we divide any number by zero, it leads to infinity, and we get an error message. We can avoid this error message using the following three methods:

• Using NULLIF() function
• Using CASE statement
• Using SET ARITHABORT OFF

We will be creating a database first to perform SQL operations.

Query:

CREATE DATABASE Test;

Output:

Commands completed successfully show the database “Test” is created.

For all the methods we need to declare two variables that will store the values of the numerator and denominator.

DECLARE @Num1 INT;
DECLARE @Num2 INT;

After declaring the variable we have to set the values. Set the second variable value as zero.

SET @Num1=12;
SET @Num2=0;

Method 1: Using NULLIF() function

If both arguments are equal, it returns NULL. If both arguments are not equal, it returns the value of the first argument.

Syntax:

NULLIF(exp1, exp2);

Now we are using the NULLIF() function in the denominator with the second argument value zero. 

SELECT @Num1/NULLIF(@Num2,0) AS Division;

• In the SQL server, if we divide any number with a NULL value its output will be NULL.
• If the first argument is zero, it means if the Num2 value is zero, then NULLIF() function returns the NULL value.
• If the first argument is not zero, then NULLIF() function returns the value of that argument. And the division takes place as regular.

Here is the complete query.

Query:

DECLARE @Num1 INT;
DECLARE @Num2 INT;
SET @Num1=12;
SET @Num2=0;
SELECT @Num1/NULLIF(@Num2,0) AS Division;

Output:

Method 2: Using the CASE statement

The SQL CASE statement is used to check the condition and return a value. It checks the conditions until it is true and if no conditions are true it returns the value in the else part.

We have to check the value of the denominator i.e the value of the Num2 variable. If it is zero then return NULL otherwise return the regular division.

SELECT CASE
WHEN @Num2=0
THEN NULL
ELSE @Num1/@Num2
END AS Division;

Here is the complete query:

Query:

DECLARE @Num1 INT;
DECLARE @Num2 INT;
SET @Num1=12;
SET @Num2=0;
SELECT CASE
    WHEN @Num2=0
    THEN NULL
    ELSE @Num1/@Num2
END AS Division;

Output:

Method 3: SET ARITHABORT OFF

To control the behavior of queries, we can use SET methods. By default, ARITHABORT is set as ON. It terminates the query and returns an error message. If we set it OFF it will terminate and returns a NULL value.

Like ARITHBORT, we have to set ANSI_WARNINGS OFF to avoid the error message.

SET ARITHABORT OFF;
SET ANSI_WARNINGS OFF;

Here is the complete query:

Query:

SET ARITHABORT OFF;
SET ANSI_WARNINGS OFF;
DECLARE @Num1 INT;
DECLARE @Num2 INT;
SET @Num1=12;
SET @Num2=0;
Select @num1/@Num2;

Output: