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

Question

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?

Answer 1

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(divisor, 0) ... 

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

Answer 2

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.