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.

Post a comment
One comment
Jiwa 29 Aug 2015 at 8:36 am

( 2012.02.11 08:02 ) : This feeder works great as long as you fololw the directions and complete ALL the steps for setting it up. After programming the current time, you set the times you want them to eat, THEN go back to those times and select 1 rotation or 2, depending on how much you want them to have. The default is 0, so that’s why it didn’t work for me at first. Now that I have it programmed correctly it works great, right on schedule. The mounting bracket did not fit on the edge of my tank and it comes with double sided tape just in case that happens. But, I wanted to be able to easily take it down to refill/reprogram/change the batteries, so I used Velcro instead of the tape. The Velcro works great but the unit does lean a little to the side. I put a quarter under that side and it’s level again. Be sure to save the directions for when you want to reprogram, because it can be a little confusing without them.