How to avoid the "divide by zero" error in SQL?

I have this error message:

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

What is the best way to write SQL code so that I will never see this error message again?

I could do either of the following:

- Add a where clause so that my divisor is never zero

Or

- I could add a case statement, so that there is a special treatment for zero.

Is the best way to use a `NULLIF` clause?

Is there better way, or how can this be enforced?

Filter out data in using a where clause so that you don't get 0 values.

There is no magic global setting 'turn division by 0 exceptions off'. The operation has to to throw, since the mathematical meaning of x/0 is different from the NULL meaning, so it cannot return NULL.
I assume you are taking care of the obvious and your queries have conditions that should eliminate the records with the 0 divisor and never evaluate the division. The usual 'gotcha' is than most developers expect SQL to behave like procedural languages and offer logical operator short-circuit, but it does **NOT**. I recommend you read this article:

[To see links please register here]

1. Add a CHECK constraint that forces <code>Divisor</code> to be non-zero
2. Add a validator to the form so that the user cannot enter zero values into this field.

Here is a situation where you can divide by zero. The business rule is that to calculate inventory turns, you take cost of goods sold for a period, annualize it. After you have the annualized number, you divide by the average inventory for the period.

I'm looking at calculating the number of inventory turns that occur in a three month period. I have calculated that I have Cost of Goods sold during the three month period of $1,000. The annual rate of sales is $4,000 ($1,000/3)*12. The beginning inventory is 0. The ending inventory is 0. My average inventory is now 0. I have sales of $4000 per year, and no inventory. This yields an infinite number of turns. This means that all my inventory is being converted and purchased by customers.

This is a business rule of how to calculate inventory turns.

You can also do this at the beginning of the query:

SET ARITHABORT OFF
SET ANSI_WARNINGS OFF

So if you have something like `100/0` it will return NULL. I've only done this for simple queries, so I don't know how it will affect longer/complex ones.

You can handle the error appropriately when it propagates back to the calling program (or ignore it if that's what you want). In C# any errors that occur in SQL will throw an exception that I can catch and then handle in my code, just like any other error.

I agree with Beska in that you do not want to hide the error. You may not be dealing with a nuclear reactor but hiding errors in general is bad programming practice. This is one of the reasons most modern programming languages implement structured exception handling to decouple the actual return value with an error / status code. This is especially true when you are doing math. The biggest problem is that you cannot distinguish between a correctly computed 0 being returned or a 0 as the result of an error. Instead any value returned is the computed value and if anything goes wrong an exception is thrown. This will of course differ depending on how you are accessing the database and what language you are using but you should always be able to get an error message that you can deal with.

try
{
Database.ComputePercentage();
}
catch (SqlException e)
{
// now you can handle the exception or at least log that the exception was thrown if you choose not to handle it
// Exception Details: System.Data.SqlClient.SqlException: Divide by zero error encountered.
}

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.

I wrote a function a while back to handle it for my [stored procedures][1]:

print 'Creating safeDivide Stored Proc ...'
go

if exists (select * from dbo.sysobjects where name = 'safeDivide') drop function safeDivide;
go

create function dbo.safeDivide( @Numerator decimal(38,19), @divisor decimal(39,19))
returns decimal(38,19)
begin
-- **************************************************************************
-- Procedure: safeDivide()
-- Author: Ron Savage, Central, ex: 1282
-- Date: 06/22/2004
--
-- Description:
-- This function divides the first argument by the second argument after
-- checking for NULL or 0 divisors to avoid "divide by zero" errors.
-- Change History:
--
-- Date Init. Description
-- 05/14/2009 RS Updated to handle really freaking big numbers, just in
-- case. :-)
-- 05/14/2009 RS Updated to handle negative divisors.
-- **************************************************************************
declare @p_product decimal(38,19);

select @p_product = null;

if ( @divisor is not null and @divisor <> 0 and @Numerator is not null )
select @p_product = @Numerator / @divisor;

return(@p_product)
end
go

[1]:

[To see links please register here]

In case you want to return zero, in case a zero devision would happen, you can use:

SELECT COALESCE(dividend / NULLIF(divisor,0), 0) FROM sometable

For every divisor that is zero, you will get a zero in the result set.