Overcome Divide by Zero Using NULLIF
-
Posted on April 5, 2012 by Derek Dieter
-
1
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.
DECLARE @num FLOAT
SET @num = 10;
SET @iter = 5;
WHILE @iter > -5
BEGIN
SELECT @num / @iter
SET @iter = @iter - 1
END
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.
DECLARE @num FLOAT;
SET @num = 10;
SET @iter = 5;
WHILE @iter > -5
BEGIN
SELECT @num / NULLIF(@iter,0);
SET @iter = @iter - 1;
END
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.
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
This will just return the same number you are dividing by, if you encounter a NULL denominator.
- Comments (RSS)
- Trackback
- Permalink
