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