Overcome Divide by Zero Using NULLIF

Anytime we are dividing we need to think of the divide by zero scenario. Even if you think you will never encounter this with your result set, it’s advisable to guard against it because when divide by zero is encountered, an error is thrown.

The best method I’ve found to overcome this is by using the NULLIF function. This function takes two parameters and if they are equal, a NULL value is returned.

Lets take a look at an example that throws a divide by zero error.

[cc lang=”sql”]
DECLARE @iter float;
DECLARE @num float

SET @num = 10;
SET @iter = 5;

WHILE @iter > -5
BEGIN
SELECT @num / @iter

SET @iter = @iter – 1
END
[/cc]

Running the following query, we see that once the variable @iter becomes zero, we receive an error.

So the most elegant way to overcome this is to use NULLIF function and compare @iter to zero. When it does equal zero, it will instead change it to a null. And when dividing anything by NULL will equal a NULL.

[cc lang=”sql”]
DECLARE @iter float;
DECLARE @num float;

SET @num = 10;
SET @iter = 5;

WHILE @iter > -5
BEGIN

SELECT @num / NULLIF(@iter,0);

SET @iter = @iter – 1;

END
[/cc]

This executes without error, however we still receive a null as a result. If you need otherwise, then you may want to wrap the equation in an ISNULL, to return a different value.

[cc lang=”sql”]
DECLARE @iter float;
DECLARE @num float;

SET @num = 10;
SET @iter = 5;

WHILE @iter > -5
BEGIN

SELECT ISNULL(@num / NULLIF(@iter,0),@num);

SET @iter = @iter – 1;

END
[/cc]

This will just return the same number you are dividing by, if you encounter a NULL denominator.