Recently a report that had been running fine for months began failing with a Divide By Zero exception.  This report is a summary of a lot of data and is contained in a stored procedure which uses quite a few table variables to do its job.  Here’s part of it:

declare @AccountManagerRevenueByFormat table 

(FormatID int, AccountManagerRevenue money)

insert into @AccountManagerRevenueByFormat

select CreativeFormatID, sum(AmountEarned)

from lq_AccountManagerRevenueDetail amr

    inner join lq_Placement p on amr.PlacementID = p.ID

where DateRecorded between @StartDate and @EndDate

group by CreativeFormatID

Ultimately, after a bunch of such tables are created, all of the results are pulled out using a final select that joins together each of the table level variables.  One of the columns displayed is a CPM value, or “cost per thousand,” which is how online ad impressions are typically priced (this report was part of AdSignia, Lake Quincy Media’s ad network management platform).  In order to calculate CPM, the query included something like this:

isnull( isnull(Revenue,0) / ( nullif(Impressions,0) / 1000),0) [CPM],

The purpose of the various isnull and nullif functions is to guard against divide by zero exceptions and to ensure that any lack of results is shown as zero.  For instance, the following returns 0 as expected:

select isnull( isnull(1,0) / ( nullif(0,0) / 1000),0) [CPM]

And when impressions are 1000 and revenue is 1, the CPM of 1 is returned as expected:

select isnull( isnull(1,0) / ( nullif(1000,0) / 1000),0) [CPM]

However, despite all of these guards, one thing can still go wrong.  Do you see it?

…

The problem has to do with the fact that Impressions are an integer field, so by default the division is going to drop any remainder.  Thus, the following doesn’t evaluate to slightly more than 1, but rather results in a divide by zero error:

select isnull( isnull(1,0) / ( nullif(999,0) / 1000),0) [CPM]

To counteract this, the Impressions field needs to be converted to a numeric or floating point data type before being divided, like so:

select isnull( isnull(1,0) / ( nullif(convert(float,999),0) / 1000),0) [CPM]

which results in: 1.001001001001.

Returning to the original snippet, it looks like this:

isnull( isnull(Revenue,0) / ( nullif(CONVERT(float,Impressions),0) / 1000),0) [CPM],