Handling Divide BY Zero Exception\ Error in SQL by NULLIF and ISNULL function

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