Code Your Way Corner Handle Divide-By-Zero Error In SQL

The idea here is that, as with any other form of math that we know of, we cannot divide by zero in a SQL call. Therefore, running this code :

 SELECT  ( 3765 / 0 ) AS value  ;  

if  you ever tried this , result of SQL server would be  :

To prevent this sort of error from being thrown, author Hugo Kornelis suggests using a NULLIF() in the divisor of the equation. NULLIF() takes two arguments and returns NULL if the two values are the same and can be used to turn the divisor from a zero into a NULL which, in turn, will force the entire equation to become NULL. 

  SELECT  ( 3765/ NULLIF( 0, 0 ) ) AS value  ;  

Therefore, while we try running modified query (as above) , we would end up getting Null Object , which reduces the risk of handling divide by zero :

NULLIF(0,0) returns NULL since zero is equal to zero , which makes SQL statement to return NULL . Till this point its pointless example since both zero values are hard coded .

But imagine if in case it was a user-centered value , or a SQL aggregate or any other calculated  value it would be of great help in handling error.

What if we want a default value when there is NULL ? It's just simple . Just use another inbuilt function like ISNULL() or COALESCE() . Query and Result would look like :

Explanation :
We are performing division in inner-query , but then if it returns NULL , then ISNULL() function would catch it and return Zero as default value . 

This would be handy for those who runs tons of reports on DB table.

Sources : Hugo_kornelis Blog

via MJ-Space

---

Other Sites :
Code Your Way | Travel Guide