Divide By Zero

Often times I get to write calculations where the it is necessary to divide one value from another.  Simple little math equations.  But in many of these cases, the denominator has the chance to be valued at zero (0).  And for these, I usually write my SQL statements similar to the following:

Declare @Numerator decimal(6,2), @Denominator decimal(6,2)

Select @Numerator = 1, @Denominator = 0

Select Case When @Denominator = 0 Then 0
Else @Numerator / @Denominator End

A simple case statement that evaluates the denominator.  This works all fine and dandy.  But the above is an ideal situation for this type of processing.  In most cases, the division will be part of a result set and sometimes the denominator is the result of some other equation.  Such as below:

Select Case When (Denominator * Factor) = 0 Then 0
Else Numerator / (Denominator * Factor) End
From dbo.Table1

In simple equations, this can be bearable but as the complexity of the calculation increases, so does the complexity of the case statement.  In complex calcuations, this can also degrade the performance of the resultset.  As an alternative, I came across a couple simple functions that used together replaces the case statement with a simple alternative.

Select Coalesce(@Numerator / NULLIF(@Denominator, 0), 0)
From dbo.Table1

Ta-da… all this writting to make divide by zero calcualtions.