Menu
Besides readability is there any significant benifit to using a CASE WHEN statement vs ISNULL/NULLIF when guarding against a divide by 0 error in SQL?
CASE WHEN (BeginningQuantity + BAdjustedQuantity)=0 THEN 0
ELSE EndingQuantity/(BeginningQuantity + BAdjustedQuantity) END
vs
ISNULL((EndingQuantity)/NULLIF(BeginningQuantity + BAdjustedQuantity,0),0)
829
ISNULL( ) function replaces the Null value with placed value. The use of ISNULL ( ) function is very common in different situations such as changing the Null value to some value in Joins, in Select statement etc. NULLIF ( ) function returns us Null if two arguments passed to functions are equal.
ISNULL() Function, COALESCE() Function And CASE Statement In our Table data, some columns contain null values. We can replace it using ISNULL() function , COALESCE() function, and CASE Statement.
advantage that COALESCE has over ISNULL is that it supports more than two inputs, whereas ISNULL supports only two. Another advantage of COALESCE is that it's a standard function (namely, defined by the ISO/ANSI SQL standards), whereas ISNULL is T-SQL–specific.
NULLIF returns the first expression if the two expressions are not equal. If the expressions are equal, NULLIF returns a null value of the type of the first expression.
Remember that NULL is different from 0. So the two code snippets in the question can return different results for the same input.
For example, if BeginningQuantity is NULL, the first expression evaluates to NULL:
CASE WHEN (NULL + ?)=0 THEN 0 ELSE ?/(NULL + ?) END
Now (NULL + ?) equals NULL, and NULL=0 is false, so the ELSE clause is evaluated, giving ?/(NULL+?), which results in NULL. However, the second expression becomes:
ISNULL((?)/NULLIF(NULL + ?,0),0)
Here NULL+? becomes NULL, and because NULL is not equal to 0, the NULLIF returns the first expression, which is NULL. The outer ISNULL catches this and returns 0.
So, make up your mind: are you guarding against divison by zero, or divison by NULL? ;-)
142
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
