Divide-by-zero errors are one of the first errors new programmers come across. A reasonable fix is to check the denominator and, if it equals zero, provide some useful result as an alternative to running the division operation.

In a table you must think in sets of records; so what happens when one or maybe a few out of the many records in your result set calculates to a divide-by-zero error? Then your entire result fails, too.

Consider the following simple temporary table with two columns, one representing a numerator (or a number to be divided) and one the denominator (the number to divide by).

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
-- Drop any pre-existing temp table used in our example if object_id('tempdb..#TestExample') is not null drop table #TestExample; go create table #TestExample(Numerator float, Denominator float); go insert into #TestExample(Numerator, Denominator) values (5,5) , (3,6) , (6,0) , (0,7) , (8,7); |

This table has 5 records. Note the third record has a denominator of zero.

Now if you run a simple select statement to divide the numerator by the denominator it will fail because of that third record.

1 2 |
-- fails because of divide by zero error select Numerator / Denominator as Answer from #TestExample; |

Fortunately T-SQL allows you to process each row conditionally with the case statement. In this case, if the result will cause a divide-by-zero error, we’ll return a zero.

1 2 3 4 5 6 7 |
-- succeeds select case when Denominator = 0 then 0 else Numerator / Denominator end as Answer from #TestExample |

The answer zero may not always be appropriate. For instance, there’s no way to tell if the answer truly was zero or if a divide-by-zero error occurred. Sometimes that’s what you want. Normally it isn’t.

The true answer to a divide-by-zero error is null because null can be anything. In calculus, 0 divided by 0 usually depends on the limit and might be a very understandable number. Non-mathematicians consider any non-zero number divided by zero as infinity although mathematicians think about such things harder than I do and might again disagree. One thing we might all agree on is we don’t know what the answer is so null is appropriate. How would you return null as a result?

1 2 3 4 5 6 7 |
select Numerator / case Denominator when 0 then null else Denominator end as Answer from #TestExample |

You can see the null propagates out of the replaced numerator into the returned answer. Alternatively you could have written your case statement this way:

1 2 3 4 5 6 |
select case when Denominator = 0 then null else Numerator / Denominator end as Answer from #TestExample |

As you can see the above case statement is similar to the original case statement that returned zero in place of a divide-by-zero. You could then replace the null result with any result you deemed appropriate.

Finally you will want to clean up your temporary table.

1 |
if object_id('tempdb..#TestExample') is not null drop table #TestExample; |

I hope you enjoyed this simple little tutorial on handling divide-by-zero errors in T-SQL. You should learn more about us.