T-SQL Divide-By-Zero

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.