Someone in my day job this week asked me how to deal with Msg 8134, Level 16, State 1, Line 3 Divide by zero error encountered error that they were receiving. It’s second nature for me to add the below code whenever I do divison. Whether I think there are going to be Zero’s in my data or not, I feel it’s better to be safe than sorry! It also reminded me that there are new people that might need this tip, so here goes:
-- Msg 8134, Level 16, State 1, Line 3 Divide by zero error encountered.
SELECT 100 / 0 as DivideByZero
-- Easy fix NULLIF(YourDivisor,0) as DivideByZero
SELECT 100 / NULLIF(0,0)
So how does it work? Nice and simple, it takes your divisor/dividend column and replaces it with NULL if its a Zero. Anything that is divided by NULL will return a NULL and you wont get the error message.