Well, as the error message says, whenever we have a division within a SQL query and the denominator has the value of zero (0) we get this error.

The exact error we get in SQL Server is:

Msg 8134, Level 16, State 1, Line [Here goes the line of the denominator in your query]

Divide by zero error encountered.

So, which is the best way of addressing this issue? As it is data-related we should be precautious anyway when having a division within a query and effectively control the denominator values for handling the case where a zero might be produced.

Just for reproducing the divide by zero error, consider the following example query:

declare @denominator int

set @denominator=0

select 1/@denominator

There are a few approaches of handling such problem. Here I present three.

Approach 1 - Using the "SET ANSI_WARNINGS OFF" Command

By using the "SET ANSI_WARNINGS OFF" command just right before the rest of the queries, it will allow your query that produces the error not to stop the execution of the rest of the queries.

Example:

SET ANSI_WARNINGS OFF

declare @denominator int

set @denominator=0

select 1/@denominator

.... Other queries go here

Approach 2 - Using the CASE Statement

By using the CASE statement it is possible to check the denominator value for a zero and if it is so you can use 1 in order for the division not to fail.

Example Query:

declare @denominator int

set @denominator=0

select 1/(case @denominator when 0 then 1 else @denominator end)

Alternatively, you can create a custom scalar-valued function that given an input parameter, it can check for a zero and if it is encountered it can return 1 else it should return the input:

CREATE FUNCTION check_denominator

(

-- Function parameter

@input int

)

RETURNS int

AS

BEGIN

-- Declare local variable

DECLARE @result int

-- Check for 0, if so then return 1 else return the input

SET @result =(SELECT (CASE @input when 0 then 1 else @input end))

-- Return the result

RETURN @result

END

GO

Then you can use the above function as follows:

declare @denominator int

set @denominator=0

select 1/dbo.check_denominator(@denominator)

Approach 3 - Using the NULLIF Function

Yep, by using the NULLIF function it is possible to handle the issue of a zero denominator.

But how? :)

The NULLIF function takes two arguments and if they have equal values it then returns a NULL.

The idea here is to compare the denominator value with a zero via NULLIF and if it returns a NULL then to handle it with the ISNULL function (by placing the number 1)!

Example:

declare @denominator int

set @denominator=0

select 1/ISNULL(NULLIF(@denominator,0),1)

Concluding Remarks

Which of the above three aproaches is the best one? Well, this is up to you :)

Personally I do not prefer Approach 1 as it does not solve the problem but rather "says" to SQL Server to ignore it.

So we have Approach 2 and 3 left. Approach 2 looks appealing but still I would only use it with a function.

My personal opinion is that Approach 3 is the best one; it just uses two built-in SQL Server functions and you do not need to write much additional code for handling a zero denominator!

Tip: Also, whenever you have a division in your query keep in mind that if you use only integer variables (like in this example :) and the calculated denominator value is below zero it will return a zero so be careful with that as well (you can use float or decimal instead)!

Hope this was useful.

Until next time!

--

**My Latest Projects:**

The exact error we get in SQL Server is:

Msg 8134, Level 16, State 1, Line [Here goes the line of the denominator in your query]

Divide by zero error encountered.

So, which is the best way of addressing this issue? As it is data-related we should be precautious anyway when having a division within a query and effectively control the denominator values for handling the case where a zero might be produced.

Just for reproducing the divide by zero error, consider the following example query:

declare @denominator int

set @denominator=0

select 1/@denominator

There are a few approaches of handling such problem. Here I present three.

Approach 1 - Using the "SET ANSI_WARNINGS OFF" Command

By using the "SET ANSI_WARNINGS OFF" command just right before the rest of the queries, it will allow your query that produces the error not to stop the execution of the rest of the queries.

Example:

SET ANSI_WARNINGS OFF

declare @denominator int

set @denominator=0

select 1/@denominator

.... Other queries go here

Approach 2 - Using the CASE Statement

By using the CASE statement it is possible to check the denominator value for a zero and if it is so you can use 1 in order for the division not to fail.

Example Query:

declare @denominator int

set @denominator=0

select 1/(case @denominator when 0 then 1 else @denominator end)

Alternatively, you can create a custom scalar-valued function that given an input parameter, it can check for a zero and if it is encountered it can return 1 else it should return the input:

CREATE FUNCTION check_denominator

(

-- Function parameter

@input int

)

RETURNS int

AS

BEGIN

-- Declare local variable

DECLARE @result int

-- Check for 0, if so then return 1 else return the input

SET @result =(SELECT (CASE @input when 0 then 1 else @input end))

-- Return the result

RETURN @result

END

GO

Then you can use the above function as follows:

declare @denominator int

set @denominator=0

select 1/dbo.check_denominator(@denominator)

Approach 3 - Using the NULLIF Function

Yep, by using the NULLIF function it is possible to handle the issue of a zero denominator.

But how? :)

The NULLIF function takes two arguments and if they have equal values it then returns a NULL.

The idea here is to compare the denominator value with a zero via NULLIF and if it returns a NULL then to handle it with the ISNULL function (by placing the number 1)!

Example:

declare @denominator int

set @denominator=0

select 1/ISNULL(NULLIF(@denominator,0),1)

Concluding Remarks

Which of the above three aproaches is the best one? Well, this is up to you :)

Personally I do not prefer Approach 1 as it does not solve the problem but rather "says" to SQL Server to ignore it.

So we have Approach 2 and 3 left. Approach 2 looks appealing but still I would only use it with a function.

My personal opinion is that Approach 3 is the best one; it just uses two built-in SQL Server functions and you do not need to write much additional code for handling a zero denominator!

Tip: Also, whenever you have a division in your query keep in mind that if you use only integer variables (like in this example :) and the calculated denominator value is below zero it will return a zero so be careful with that as well (you can use float or decimal instead)!

Hope this was useful.

Until next time!

--

**DBA Security Advisor:**Secure your SQL Server instances against security risks.**In-Memory OLTP Simulator:**Easily benchmark SQL Server's In-Memory OLTP Engine against your custom workload.**Artemiou SQL Books:**Download my latest free books on SQL Server.**Artemiou Data Tools:**See my latest software projects.

Labels: SQL Server, SQL Server Development, SQL Server Troubleshooting