Handling Division By Zero Scenarios in T-SQL

Sometimes it is inevitable to encounter scenarios that will give division by zero errors

[sql collapse=”false” firstline=”1″ gutter=”true” smarttabs=”true” tabsize=”4″ toolbar=”true”]

DECLARE @dividend INT
DECLARE @divisor INT

SET @dividend = 1
SET @divisor = 0

SELECT @dividend/@divisor

/*
Error:
Msg 8134, Level 16, State 1, Line 7
Divide by zero error encountered.
*/

[/sql]

What you can do is you can code around it, so your users and your app do not get this error.

Alternative 1: NULLIF (preferred)

The NULLIF built in function returns a NULL if the two parameters are equal. In our case, we want to check if the divisor is zero.

[sql collapse=”false” firstline=”1″ gutter=”true” smarttabs=”true” tabsize=”4″ toolbar=”true”]

DECLARE @dividend INT
DECLARE @divisor INT

SET @dividend = 1
SET @divisor = 0

SELECT @dividend/NULLIF(@divisor,0)

/*
Returns NULL
*/

[/sql]

Alternatively, instead of NULL, you may want to display just 0

[sql collapse=”false” firstline=”1″ gutter=”true” smarttabs=”true” tabsize=”4″ toolbar=”true”]

SELECT ISNULL(@dividend/NULLIF(@divisor,0),0)

/*
Returns NULL, no error
*/

[/sql]

Alternative 2: CASE

You can also use CASE to drive what values you want to show if the divisor. The downside to this approach is your code can get really lengthy right away by having multiple CASE statements.

[sql collapse=”false” firstline=”1″ gutter=”true” smarttabs=”true” tabsize=”4″ toolbar=”true”]

SELECT
CASE @divisor
WHEN 0 THEN 0
ELSE @dividend/NULLIF(@divisor,0)
END

/*
Returns 0, no error
*/

[/sql]

Alternative 3: IF/ELSE

You can also use IF/ELSE. However this means you cannot just have one SELECT statement. This needs to be in a script, a stored proc, or UDF.

[sql collapse=”false” firstline=”1″ gutter=”true” smarttabs=”true” tabsize=”4″ toolbar=”true”]

IF @divisor = 0
BEGIN
SELECT 0
END
ELSE BEGIN
SELECT @dividend/@divisor
END

/*
Returns 0, no error
*/

[/sql]

There you go. No more division by zero woes in T-SQL.

Advertisements

6 comments

  • I’ve been searching the net for this kind of solution. While i’m sure your solution works, however, it doesn’t seem to work in my situation. My divide by zero occurs in a SQL Server view when i try to find the MOD (remainder) of 2 columns. In very few instances the divisor is 0. By trying to apply the concepts you show i still get the devide by 0 error. Any suggestions for use in a view?

    Like

  • These approaches will surely avoid Division by Zero errors – but they do not solve the problem. Usually you should ask yourself why there is a /0-problem. If you have this answer you can query if it is a querying fault (in many cases) or if it happens by design.
    In the first case you should optimize your query and only in the latter case you should handle the issue by asking you which result you are expecting when the problem occurs. That’s when you use the above mentioned techniques.
    @Gary: If you solved a /0-problem and it’s still there you have another one. It can be in the order by clause or somewhere else. It is even possible that a subquery results 0 records while you are expecting more …

    Like

  • None of that works on something like this…

    (SELECT SUM(Cost) AS Expr1
    FROM dbo.qry_367costs AS x
    WHERE ([Week No] = dbo.qry_367costs.[Week No]) AND (Year = dbo.qry_367costs.Year)) /
    (SELECT COUNT(TransID) AS Expr1
    FROM dbo.qry_sitecISQ AS qry_sitecISQ_2
    WHERE (Tinweek = dbo.qry_367costs.[Week No]) AND (Tinyear = dbo.qry_367costs.Year)) AS AvgCPQ

    Like

  • @Peter North Maybe that’s due to the way SQL Server treats (ignores) NULLs when aggregating.

    Like

  • Great article!

    Of course there are real scenario’s where /0 can happen!

    In my case, I want to return retail sales values for days even if the store is closed, to maintain values for each day of the week.

    Sunday – $1000
    Monday – $1000
    Tuesday – $1000
    Wednesday – $1000
    Thursday – $1000
    Friday – $1000
    Saturday – $0

    Our business requirements calls for the day to appear in reporting, even if no retail activity. Sales / Transactions = Average Ticket

    Thanks again.

    Like

  • Pingback: Handling Division By Zero Scenarios in T-SQL | code siblings,adventures,songs and excerpts of my daily work

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