When performing calculations in T-SQL, I found myself having to deal with zeros in division operations. While zeros in the numerator pose no threat, denominators of zero will cause your query to blow up. In order to fix divide by zero errors, I began writing CASE
statements. However, this got old very quickly. The stored procedure I was working on had tons of division operations. Writing CASE
statements for all of these would be insane. There had to be a better way! Enter NULLIF
, the extremely easy way to fix divide by zero errors in T-SQL.
NULLIF
will return null when its arguments are equal. For division, you simply pass the denominator as the first argument, and 0 as the second. Voila! The quotient of division by a null value is, you guessed it, null
!
Without NULLIF
, the following query will net you a “Divide by zero error” if valueDenominator
is ever 0:
SELECT (valueNumerator / valueDenominator) AS Quotient FROM Values_TBL |
To fix divide by zero errors, just wrap your denominator with NULLIF
:
SELECT (valueNumerator / NULLIF(valueDenominator,0)) AS Quotient FROM Values_TBL |
Sure, for simple cases you could also add WHERE valueDenominator != 0
. But when valueDenominator
is itself a complex calculation, or when you want a row in the result set even when valueDenominator
is null, you won’t want this where
clause.
Right away I am going to do my breakfast, once having my breakfast coming yet again to
read additional news.