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).

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.

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.

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?

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:

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.

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

Like this post? Share it!