I have been writing queries of one type or another for several years and for the last year and a half I have been trying to learn MSSQL. Although most of the syntax is like every other SQL language, the differences are enough to make me struggle.

One particular item that has always flustered me in the realm of report generating is the dreaded “divide by zero” error. I have been using ColdFusion for handling this through a method I call isZero(value, return_if_zero_value) where I pass it a value and a value I want returned if value is zero. This has never really been a good solution as the divide value is often something it should not be (should be zero).

I was frustrated with my inability to resolve this correctly and decided to spend some time trying to solve this.

I know that my select statement below will fail when t1.IMPRESSIONS == 0

SELECT (CONVERT(DECIMAL(10,5), t1.CLICKS) / CONVERT(DECIMAL(10,5), t1.IMPRESSIONS)) * 100 AS CLICK_RATE

I scoured through the SQL documentation and after about 20-30 minutes, I had one of those ‘Eureka!’ moments. What if I could use IsNULL() method in SQL? If I could return null for a value, then maybe I could use IsNULL() to return zero when the divisor is zero. The only way I could do this is to return a null value when the divisor is zero, then correct? Enter the NULLIf() method.

IsNULL( DIVIDEND / NULLIf(DIVISOR, 0), 0)

I thought I just created something ground breaking, but in fact another few google searches and I found that this is really the most common way for most experienced developers to write out the SQL. Here is the end result of the SELECT statement.

SELECT (IsNULL(CONVERT(DECIMAL(10,5), t1.CLICKS) / NULLIf(CONVERT(DECIMAL(10,5), t1.IMPRESSIONS), 0), 0)
* 100 AS CLICK_RATE

I hope that this will help someone in the future. I know I could have used it about a year ago.