SQL Query Divide By Zero Alternative

We’ve all been there. In a haste to write a quick SQL query that turns into a production query we take one value as a numerator and another as a denominator and put that division slash between them and move on. It works great, except only to find when we show the results off in production to our stakeholders the query crashes with a message like this:

We then scramble to figure out where and why, all the while the stakeholder gets impatient while their super smart person looks like a fool. The sweat starts to bead up, the nervous chuckles ensue. Rapid typing and scrolling, executing the query over and over again fixing every little possible problem. Right? Then you utter, “It worked when I tested it.”

A hasty SQL solution

Since we’ve all been there, we know how easy it is to fix the query to not divide by zero. We have all used IF ~~~ THEN, CASE WHEN ~~~ THEN, ISNULL, IFNULL, NULLIF or whatever function for whatever RDBMS (Relational Database Management System) is our flavor of choice or any combination of these functions as well. To get the math close enough, we have probably just slapped some parenthesis around the denominator and added .00000001 to it just to get the query to return a value close enough.

Don’t laugh! You did it, I know you did.

A better SQL solution

Well, many years ago it occurred to me to quit getting caught with my pants down and just build a function to handle this for those embarrassing moments. That little UDF (User-Defined Function) has come in quite handy over the years. I typically work with SQL Server, but there are many ways to write this across any RDBMS. Here is the Transact-SQL:

CREATE FUNCTION [dbo].[SafeDivide]
(
	@Numerator FLOAT,
	@Denominator FLOAT,
	@IfDivideByZeroDefault FLOAT
)
RETURNS FLOAT
AS
BEGIN
	RETURN (
		CASE WHEN @Denominator = 0 THEN 
			@IfDivideByZeroDefault
		ELSE 
			@Numerator / @Denominator
		END
	)
END

That’s it! That simple little function has saved me time and embarrassment over the years. Here is how you use it:

You’ll see the first query returns the division result as it is dividing 100 by 2, which is 50. The second query is dividing 100 by 0, which is not possible, so it uses the default value of 777 as the result. Typically for the default value I choose 0, but there are some cases you might want to use 1 or 100 if you are working with percents. It just depends on the use case.

A happy SQL ending

As you can see, this simple UDF will save a lot of slow starts to meetings with stakeholders when your awesome query backfires with production data or the first time they ask you to change the date range outside the one you tested with.

I hope it helps you as much as it has me over the years and cuts down on your monthly antiperspirant bill. Happy coding!

---

We use SQL all the time when we develop software solutions. Read about our Software Development service and read a case study about how we were able to help a small business with our services.