Handling Divide BY Zero Exception\ Error in SQL by NULLIF and ISNULL function
Some times while doing Calculation in your query you got an error or exception "Divide by Zero" and some time you will have Output value Like NULL So how to handle these Issue and Exception.
Use NULLIF -To Handle Divide by zero Exception
Use ISNULL -To Show some value instead of NULL in your output
Below is the complete explanation
select 10/0
if you run the above query it will throw an error
Msg 8134, Level 16, State 1, Line 1
Divide by zero error encountered.
Now in order to Solve this we will use Nullif function
select nullif (10/ nullif (0,0),0)
Output NULL
Now it will not throw an error and your output will be null
Explanation:NULLIF function takes two arguments check wheather they are equal or not if they are equal it will return NULL as output
IF both expressions are not equal it will return the first expression with same data type.
now in the denominator it checked wheather 0 is equal to 0
The conditon was true it returned Null in denominator
now the expression was like
select Nullif(10/null,0)
now again it will check wheather 10/Null is equal to zero condition fails
and you will get
Output NULL
Note : AnyNumber divide multiplied,added subtracted with NULL will Resultant to NULL only.
Now how to use NULLIF and ISNULL in your Code or in TSQL to avoid exception and NULL Values .
In the below code i have used nullif with ISNULL function .
For this we will Create a table
CREATE TABLE Test_NULLIF1
(
col1 int IDENTITY,
col2 int NULL,
col3 int NULL
);
INSERT Test_NULLIF1 VALUES(10, 20);
INSERT Test_NULLIF1 VALUES(NULL, 23);
INSERT Test_NULLIF1 VALUES(0, 18);
INSERT Test_NULLIF1 VALUES(NULL,75);
INSERT Test_NULLIF1 VALUES(300000, 21);
Go
If you will divide column 3 by column 2 for Row 3 it will throw Divide By zero Exception
Run this query to generate error..
select col3/col2 from Test_NULLIF1 where col1 =3
Msg 8134, Level 16, State 1, Line 1
Divide by zero error encountered.
In order to handle this Use nullif function as explained in above example
select nullif(col3/nullif(col2,0),0) from Test_NULLIF1 where col1 =3
Output NULL
Now You can Use ISNULL function after handling divide by zero exception to give a value when you have output like NULL
for eg like instead of NULL you want to show value like 0 or 1 or anything
select ISNULL(col3/nullif(col2,0),1) from Test_NULLIF1 where col1 =3
Output 1
Explanation: ISNULL Function takes two argument check wheather first expression is NULL
if null provide a replacement for that NULL in second expression it can be any value.
IN query ISNULL checked the first expression it was NULL so it replaced the NULL Value with 1,
and return output as 1.
IN case if first expression is not null it willl return the first expression value only. .
Test IsNULL function with below queries
eg
Select ISNULL(null,2)
output 2
select isnull(3,1)--as first expression is not null so it returns first value i.e. 3
output 3