Division by zero

The error message for division by 0 is a common problem in the SQL server:

DECLARE @dividend INT
DECLARE @divisor INT

SET @dividend = 1
SET @divisor = 0

SELECT @dividend / @divisor

/*
Return value:
Message 8134, level 16, status 1, line 7.
Error due to division by zero.
*/

This article will discuss various possibilities to catch this error message in the SQL server when dividing by 0. Often such errors only happen after a longer period of time, i.e. when the underlying tables / columns grow due to the data. What possibilities are there to avoid this error?

Alternative 1: CASE

DECLARE @dividend INT
DECLARE @divisor INT

SET @dividend = 1
SET @divisor = 0

SELECT CASE
WHEN @divisor = 0 THEN NULL
ELSE @dividend / @divisor
END

/*
Return value = NULL
*/

Alternative 2: NULLIF

DECLARE @dividend INT
DECLARE @divisor INT

SET @dividend = 1
SET @divisor = 0

SELECT@dividend / NULLIF(@divisor , 0)

/*
Return value = NULL
*/

Alternative 3: NULLIF with alternative return value

DECLARE @dividend INT
DECLARE @divisor INT

SET @dividend = 1
SET @divisor = 0

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

/*
Return value = 0
*/

Alternative 4: Eine einfache Funktion

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE NAME = ‘division’) DROP FUNCTION division
go
CREATE FUNCTION dbo.division( @dividend decimal(38, 10), @divisor decimal(38, 10)) returns decimal(38, 10)
BEGIN
DECLARE @ergebnis decimal(38, 10);
SELECT @ergebnis = NULL;
IF ( @divisor IS NOT NULL
AND @divisor <> 0
AND @dividend IS NOT NULL )
SELECT @ergebnis = @dividend / @divisor;
RETURN( @ergebnis )
END

go

Alternative 5: ARITHABORT

Terminates a query if an overflow error or an error due to a division by 0 (zero) occurs during the query.

SET ARITHABORT OFF
SET ANSI_WARNINGS OFF

DECLARE @dividend INT
DECLARE @divisor INT

SET @dividend = 1
SET @divisor = 0

SELECT @dividend / @divisor

/*
Return value = NULL
*/

SET ARITHABORT ON
SET ANSI_WARNINGS ON

The SET ARITHABORT setting is set at execution time and not at analysis time. Even if SET ARITHABORT is set to OFF and SET ANSI_WARNINGS is set to ON, the SQL Server will return an error message if an error occurs due to a division by zero or an overflow error.

To query which setting is set for ARITHABORT, you can execute the following code:

DECLARE @ARITHABORT VARCHAR(3) = 'OFF';
IF ( (64 & @@OPTIONS) = 64 ) SET @ARITHABORT = 'ON';
SELECT @ARITHABORT AS ARITHABORT;

Which method is best always depends on the case at hand. Do you have any questions? Then feel free to contact our experts via our contact page or by phone at +49 7331 3076 - 0.