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.