Handling 0 (Zero) in Denominators in SQL Server

 We can handle 0’s (zeros) in Denominators in SQL Server by using NULLIF statement. Below is an example:

 — Declaring the variables

DECLARE @Numerator INT, @Denominator INT

 — Assigning the vaues to the variables

SET @Numerator = 4

SET @Denominator = 0

 — Dividing the value with denominator as 0

SELECT @Numerator / @Denominator

 By executing the above batch of statements, we will get below error:

 Msg 8134, Level 16, State 1, Line 9

Divide by zero error encountered.

 In order to overcome the above error execute as below in SSMS:

 — Declaring the variables

 DECLARE @Numerator INT, @Denominator INT

 — Assigning the vaues to the variables

 SET @Numenator = 4

 SET @Denominator = 0

 — Dividing the value with denominator as 0

 SELECT ISNULL( @Numerator /NULLIF(@Denominator,0),0 )

 NULLIF (in denominator) in the above statement makes Null, if it finds the value of the variable @Denominator as 0, ISNULL (in Numerator) makes the value to 0 if it finds the result of the divided value as Null

Read Full Post »