Here is one of the most popular questions: How to overcome (Error 8134) Divide by Zero Error Encountered in SQL Server?
Before we see the answer of this question, let us see how to recreate this error.
Run following script in SQL Server Management Studio window.
DECLARE @Var1 FLOAT; DECLARE @Var2 FLOAT; SET @Var1 = 1; SET @Var2 = 0; SELECT @Var1/@Var2 MyValue;
When you execute above script you will see that it will throw error 8134.
Msg 8134, Level 16, State 1, Line 6
Divide by zero error encountered.
Here is the screenshot of the error.
Now there are multiple ways to avoid this error to happen. We will see two of the most popular methods to overcome this error.
Method 1: Use NullIf Function
Here is the SQL script with NullIf Function
DECLARE @Var1 FLOAT; DECLARE @Var2 FLOAT; SET @Var1 = 1; SET @Var2 = 0; SELECT @Var1/NULLIF(@Var2,0) MyValue;
When you use the NULLIF function, it converts the zero value to Null and leading to the entire result set to be NULL instead of an error.
Method 2: Use Case Statement
Here is the SQL script with CASE Statement
DECLARE @Var1 FLOAT; DECLARE @Var2 FLOAT; SET @Var1 = 1; SET @Var2 = 0; SELECT CASE WHEN @Var2 = 0 THEN NULL ELSE @Var1/@Var2 END MyValue;
When you use CASE statement, it converts the zero value to Null and leading to the entire result set to be NULL instead of an error.
Let me know if you have any other alternate solution. I will be happy to publish in the blog with due credit.
Reference: Pinal Dave (https://blog.sqlauthority.com)
10 Comments. Leave new
Hi,
Please check the below code to avoid 8134 error.
DECLARE @Var1 FLOAT;
DECLARE @Var2 FLOAT;
SET @Var1 = 1;
SET @Var2 = ”; –0, 1, NULL,”
IF(@Var2=0)
SELECT NULL;
ELSE
SELECT @Var1/@Var2;
Regards,
SubbaReddy AV
That should work as well. Thanks for sharing.
begin try
select @var1/@var2
end try
begin catch
if error_number() = 8134 select null
else select error_number()
end catch
A solution
DECLARE @Var1 FLOAT;
DECLARE @Var2 FLOAT;
SET @Var1 = 1;
SET @Var2 = 0;
SELECT nullif((@Var1/@Var2),0);
Disclaimer: I don’t recommend this, I’d rather see it handled via code, but it works.
SET ARITHABORT OFF;
SET ANSI_WARNINGS OFF;
DECLARE @Var1 FLOAT = 1, @Var2 FLOAT = 0;
SELECT @Var1 / @Var2;
DECLARE @Var1 FLOAT;
DECLARE @Var2 FLOAT;
SET @Var1 = 1;
SET @Var2 = 0;
SELECT @Var1/IIF(@Var20,@Var2,Null)MyValue;
Good one.
I don’t think returning NULL will solve the problem. For example what will happen if we get divided by zero exception in aggregate function?
In that case as well, it should be handled. Either send NULL and send proper error message
it works well… Great to learn