views:

4000

answers:

11

I hate 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 mean, I could 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?

+11  A: 

You'll have to give a bit of context, because without it, the obvious answer is "stop dividing by zero"

Ralph Shillington
ok, I'll try to do that
Henrik Staun Poulsen
+1  A: 

Write code to ensure that the divisor is not 0 before trying to perform the division?

Fredrik Mörk
+14  A: 

In order to avoid a "Division by zero" error we have programmed it like this:

Select Case when divisor=0 then null
Else dividend / divisor
End ,,,

But here is a much nicer way of doing it:

Select dividend / nullif(dividend, 0) ...

Now the only problem is to remember the NullIf bit, if I use the "/" key.

Henrik Staun Poulsen
that's a nice little trick :)
Peter Perháč
that's the way I would have solved it.
sheepsimulator
+2  A: 

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

nunespascal
+2  A: 

I wrote a function awhile back to handle it for my procs:

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

Ron

Ron Savage
Hi Ron, Nice solution, except it has a limited data type (4 decimal places) and our @divisors can be negative as well.And how do you enforce it's use?TIAHenrik Staun Poulsen
Henrik Staun Poulsen
I dashed it off pretty quick to handle a specific problem scenario at the time. Single developer app, so enforcement not so difficult except for my memory. :-)
Ron Savage
A: 

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: http://www.sqlmag.com/Articles/ArticleID/9148/pg/2/2.html

Remus Rusanu
thank you very much for that link.
Henrik Staun Poulsen
+4  A: 

I think there's an underlying issue here, which is that division by 0 is not legal. It's not that it's just hard or irritating, but 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 answer you can get will be valid.

Edit: So Edwardo asks in the comments "what if the user puts in a 0?" 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 do some weird math 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.

Beska
Have you ever do some real programing? There are so many times that you need to divide by zero, but no in the mathematically sense of it, but because the user put zero in the amount (or something) and you need to calculate the VAT (or something). Just returning zero is ok in this cases, we are no trying to find the end of the universe.
Eduardo Molteni
I disagree. Your business rules should never ever end up doing illegal math. If you end up doing something like this most likely your data model is wrong. Whenever you encounter a divide by 0 you should ponder if the data should had been NULL instead of 0.
Remus Rusanu
I can't believe I was downvoted by someone who asks if I've ever "do any real programming?" because I'm saying to do it right, rather than be lazy. *sigh*
Beska
I'm sorry, I didn't mean to offend you. But the question is perfectly valid in a lot of common LOB applications, and answering it with a "division by 0 is not legal" does not add value IMHO.
Eduardo Molteni
what is the best way to ensure that we always consider zero when writing the slash character?
Henrik Staun Poulsen
@Eduardo...but that's kind of my point. It's not valid there either. It may happen at times that there is an edge case, or bad user input, or something odd like that. No question about that...and it has to be handled. But my point (which I guess I didn't make very well), is that it should be handled by the business logic up front...capturing this bad/edge/whatever data and flagging it. Because you don't really want to divide by 0, which is impossible; you simply want these extra cases to be handled in an elegant way. That shouldn't be done at the SQL level, I don't think.
Beska
henrikstaunpoulsen: I think the best way is to, in general, try to handle it outisde of the SQL. If you're passing in a 0, then you must be in a strange edge case, or have bad data, or something like that...and so there's no point in going to the SQL layer anyway. Just have the code logic return an error code, or null, or whatever it should do. Then you can leave your SQL code as clean as possible, and if you ever *do* see a /0 error coming from there, it will be a good warning that you're not catching a case that you should be...it will be a good thing, rather than a bad thing.
Beska
+1  A: 

Perhaps some data validation is in order.

Anthony
A: 

SELECT Dividend / ISNULL(NULLIF(Divisor,0),1) AS Result

+2  A: 
  1. Add a CHECK constraint that forces Divisor to be non-zero
  2. Add a validator to the form so that the user cannot enter zero values into this field.
finnw
I start to like CHECK constraints more and more.
Henrik Staun Poulsen
A: 

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.

Jimmy
Fine. But at no point are you really dividing by 0. You're using a special rule to handle an edge case where your normal math formula doesn't work. You definitely don't want to have some kind of overarching rule in sql for dividing by 0 because not all special cases are handled identically.
Beska