Solution for Msg 8134 Divide by zero error encountered

Problem

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

While performing division operation in SQL Server, you may come across this error when you try to divide a number by 0.

Solution

To avoid this error, you can check and make sure that the divisor is not 0. Another option is to catch the exception in the proper way and throw the message to the front end, so as you can easily identify what went wrong and where the exception happened. Let us see both the options.

Option 1: Check for 0 and make it NULL

This is kind of suppressing the exception. Using the NULLIF function, check the divisor for 0 and change it to NULL. A number when divided by NULL will become NULL. So, there will not be any exception and the result of the division operation is NULL.

DECLARE @variable1 FLOAT,
	@variable2 FLOAT;

SELECT @variable1 = 100,
	@variable2 = 0;

SELECT @variable1 / NULLIF(@variable2, 0) Result;

Option 2: Handle the exception

This option of handling the exception will be helpful when you are performing mathematical operations in a stored procedure. When there are several mathematical operations, catching and throwing the exception outside the stored procedure will be greatly helpful to debug and figure out the problem. Let’s see an example.

Here is a stored procedure with a division operation and exception handling. This procedure has two output parameters, one to get the result and another to get the error message.

/** Create Stored procedure **/
CREATE PROCEDURE mtb_DivisionOperation
	@Value1 FLOAT,
	@Value2 FLOAT,
	@Result FLOAT OUTPUT,
	@Error NVARCHAR(MAX) OUTPUT
AS
BEGIN
	BEGIN TRY

        SET @Result = @Value1 / @Value2;

    END TRY
    BEGIN CATCH
        SET @Error = 'Error Number: ' + CAST(ERROR_NUMBER() AS VARCHAR(10)) + '; ' + Char(10) +
        'Error Severity: ' + CAST(ERROR_SEVERITY() AS VARCHAR(10)) + '; ' + Char(10) +
        'Error State: ' + CAST(ERROR_STATE() AS VARCHAR(10)) + '; ' + Char(10) +
        'Error Line: ' + CAST(ERROR_LINE() AS VARCHAR(10)) + '; ' + Char(10) +
        'Error Message: ' + ERROR_MESSAGE()
    END CATCH
END
GO

When there divisor is 0, then the @Result output parameter will be NULL and the @ErrorMsg output parameter will have the error details.

/** Execute Stored Procedure **/
DECLARE @ResultVal FLOAT
DECLARE @ErrorMsg NVARCHAR(MAX)

EXEC mtb_DivisionOperation 
	@Value1 = 100, @Value2 = 0,  
	@Result = @ResultVal OUTPUT, @Error = @ErrorMsg OUTPUT

SELECT @ResultVal AS 'Result'
SELECT @ErrorMsg AS 'Error Message'
GO

/** Results **/

Result
----------------------
NULL

(1 row affected)

Error Message
----------------------
Error Number: 8134; 
Error Severity: 16; 
Error State: 1; 
Error Line: 11; 
Error Message: Divide by zero error encountered.

(1 row affected)

/** Execute Stored Procedure **/
DECLARE @ResultVal FLOAT
DECLARE @ErrorMsg NVARCHAR(MAX)

EXEC mtb_DivisionOperation 
	@Value1 = 100, @Value2 = 3,  
	@Result = @ResultVal OUTPUT, @Error = @ErrorMsg OUTPUT

SELECT @ResultVal AS 'Result'
SELECT @ErrorMsg AS 'Error Message'
GO

/** Results **/

Result
----------------------
33.3333333333333

(1 row affected)

Error Message
----------------------
NULL

(1 row affected)

Reference

• About TRY-CATCH blocks in T-SQL at Microsoft Docs.