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:

 <strong>Msg 8134, Level 16, State 1, Line 9</strong><strong></strong>

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

Leave a comment

1 Comment

  1. I enjoy browsing your site given it has very worthwhile issues.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: