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,
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
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
Right away I am going to do my breakfast, once having my breakfast coming yet again to
read additional news.