# stored - sql if zero then 1

## How to avoid the “divide by zero” error in SQL? (12)

## This seemed to be the best fix for my situation when trying to address dividing by zero, which does happen in my data.

Suppose you want to calculate the male–female ratios for various school clubs, but you discover that the following query fails and issues a divide-by-zero error when it tries to calculate ratio for the Lord of the Rings Club, which has no women:

```
SELECT club_id, males, females, males/females AS ratio
FROM school_clubs;
```

You can use the function `NULLIF`

to avoid division by zero. `NULLIF`

compares two expressions and returns null if they are equal or the first expression otherwise.

Rewrite the query as:

```
SELECT club_id, males, females, males/NULLIF(females, 0) AS ratio
FROM school_clubs;
```

Any number divided by `NULL`

gives `NULL`

, and no error is generated.

I have this error message:

Msg 8134, Level 16, State 1, Line 1 Divide by zero error encountered.

What is the best way to write SQL code so that I will never see this error message again?

I could do either of the following:

- Add a where clause so that my divisor is never zero

Or

- I could add a case statement, so that there is a special treatment for zero.

Is the best way to use a `NULLIF`

clause?

Is there better way, or how can this be enforced?

- Add a CHECK constraint that forces
`Divisor`

to be non-zero - Add a validator to the form so that the user cannot enter zero values into this field.

Filter out data in using a where clause so that you don't get 0 values.

For update SQLs:

```
update Table1 set Col1 = Col2 / ISNULL(NULLIF(Col3,0),1)
```

I wrote a function a while back to handle it for my stored procedures:

```
print 'Creating safeDivide Stored Proc ...'
go
if exists (select * from dbo.sysobjects where name = 'safeDivide') drop function safeDivide;
go
create function dbo.safeDivide( @Numerator decimal(38,19), @divisor decimal(39,19))
returns decimal(38,19)
begin
-- **************************************************************************
-- Procedure: safeDivide()
-- Author: Ron Savage, Central, ex: 1282
-- Date: 06/22/2004
--
-- Description:
-- This function divides the first argument by the second argument after
-- checking for NULL or 0 divisors to avoid "divide by zero" errors.
-- Change History:
--
-- Date Init. Description
-- 05/14/2009 RS Updated to handle really freaking big numbers, just in
-- case. :-)
-- 05/14/2009 RS Updated to handle negative divisors.
-- **************************************************************************
declare @p_product decimal(38,19);
select @p_product = null;
if ( @divisor is not null and @divisor <> 0 and @Numerator is not null )
select @p_product = @Numerator / @divisor;
return(@p_product)
end
go
```

In case you want to return zero, in case a zero devision would happen, you can use:

```
SELECT COALESCE(dividend / NULLIF(divisor,0), 0) FROM sometable
```

For every divisor that is zero, you will get a zero in the result set.

Replacing "divide by zero" with zero is controversial - but it's also not the only option. In some cases replacing with 1 is (reasonably) appropriate. I often find myself using

```
ISNULL(Numerator/NULLIF(Divisor,0),1)
```

when I'm looking at shifts in scores/counts, and want to default to 1 if I don't have data. For example

```
NewScore = OldScore * ISNULL(NewSampleScore/NULLIF(OldSampleScore,0),1)
```

More often than not, I've actually calculated this ratio somewhere else (not least because it can throw some very large adjustment factors for low denominators. In this case I'd normally control for OldSampleScore is greater than a threshold; which then precludes zero. But sometimes the 'hack' is appropriate.

There is no magic global setting 'turn division by 0 exceptions off'. The operation has to to throw, since the mathematical meaning of x/0 is different from the NULL meaning, so it cannot return NULL.
I assume you are taking care of the obvious and your queries have conditions that should eliminate the records with the 0 divisor and never evaluate the division. The usual 'gotcha' is than most developers expect SQL to behave like procedural languages and offer logical operator short-circuit, but it does **NOT**. I recommend you read this article: http://www.sqlmag.com/Articles/ArticleID/9148/pg/2/2.html

You can also do this at the beginning of the query:

```
SET ARITHABORT OFF
SET ANSI_WARNINGS OFF
```

So if you have something like `100/0`

it will return NULL. I've only done this for simple queries, so I don't know how it will affect longer/complex ones.

You can at least stop the query from breaking with an error and return `NULL`

if there is a division by zero:

```
SELECT a / NULLIF(b, 0) FROM t
```

However, I would **NEVER** convert this to Zero with `coalesce`

like it is shown in that other answer which got many upvotes. This is completely wrong in a mathematical sense, and it is even dangerous as your application will likely return wrong and misleading results.

```
CREATE FUNCTION dbo.Divide(@Numerator Real, @Denominator Real)
RETURNS Real AS
/*
Purpose: Handle Division by Zero errors
Description: User Defined Scalar Function
Parameter(s): @Numerator and @Denominator
Test it:
SELECT 'Numerator = 0' Division, dbo.fn_CORP_Divide(0,16) Results
UNION ALL
SELECT 'Denominator = 0', dbo.fn_CORP_Divide(16,0)
UNION ALL
SELECT 'Numerator is NULL', dbo.fn_CORP_Divide(NULL,16)
UNION ALL
SELECT 'Denominator is NULL', dbo.fn_CORP_Divide(16,NULL)
UNION ALL
SELECT 'Numerator & Denominator is NULL', dbo.fn_CORP_Divide(NULL,NULL)
UNION ALL
SELECT 'Numerator & Denominator = 0', dbo.fn_CORP_Divide(0,0)
UNION ALL
SELECT '16 / 4', dbo.fn_CORP_Divide(16,4)
UNION ALL
SELECT '16 / 3', dbo.fn_CORP_Divide(16,3)
*/
BEGIN
RETURN
CASE WHEN @Denominator = 0 THEN
NULL
ELSE
@Numerator / @Denominator
END
END
GO
```

```
SELECT Dividend / ISNULL(NULLIF(Divisor,0), 1) AS Result from table
```

By catching the zero with a nullif(), then the resulting null with an isnull() you can circumvent your divide by zero error.