Divide By Zero Error In Sql Server 2005
Contents |
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company Business Learn more about hiring developers or posting sql server divide by zero error encountered ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join divide by zero error encountered in sql server 2012 the Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them; it only takes a divide by zero error encountered. in sql server 2008 minute: Sign up Simple way to prevent a Divide By Zero error in SQL up vote 9 down vote favorite 2 I have a SQL query which used to cause a Divide By Zero exception, I've wrapped it in a CASE
Divide By Zero Error Encountered Excel
statement to stop this from happening. Is there a simpler way of doing this? Here's my code: Percentage = CASE WHEN AttTotal <> 0 THEN (ClubTotal/AttTotal) * 100 ELSE 0 END sql sql-server-2008 tsql sql-server-2005 share|improve this question asked Oct 28 '13 at 9:22 Denys Wessels 10.5k43872 1 It depends on how you wan't your query and result to behave when AttTotal is 0 or NULL, so there is no general way of doing it. I'd say your query is just msg 8134 level 16 state 1 line 1 divide by zero error encountered fine. Even if you write your query differently using ISNULL or NULLIF at the end it will likely execute the same. –Nenad Zivkovic Oct 28 '13 at 9:34 As has been pointed out by others, the logic in this CASE statement doesn't quite make sense to us. See my comment on my answer for a brief explanation and then choose whether you want your original answer or mine. My answer would be a more usual solution. –Tom Chantler Oct 28 '13 at 9:40 1 Possible duplicate of How to avoid the "divide by zero" error in SQL? –Henrik Staun Poulsen Mar 10 at 21:06 add a comment| 5 Answers 5 active oldest votes up vote 26 down vote accepted A nicer way of doing this is to use NULLIF like this: Percentage = 100 * ClubTotal / NULLIF(AttTotal, 0) share|improve this answer answered Oct 28 '13 at 9:25 Tom Chantler 11.2k42940 2 This will return NULL if AttTotal = 0, while the query in question returns 0. –Nenad Zivkovic Oct 28 '13 at 9:31 3 @NenadZivkovic - Returning NULL for a divide by zero error probably makes more mathematical sense anyway though. –Martin Smith Oct 28 '13 at 9:34 I agree with @Martin Smith. If you have a situation where the ClubTotal is a number and AttTotal is zero, it doesn't make sense to return zero. Or any other number :-) Returning zero implies ClubTotal is zero a
SERVER - How to Fix Error 8134 Divide by Zero Error Encountered August 27, 2016Pinal DaveSQL Tips and Tricks5 commentsHere is one of the most popular questions: How to overcome (Error 8134) Divide by Zero Error Encountered in SQL Server?Before we see oracle sql divide by zero the answer of this question, let us see how to recreate this error.Run
Sql Nullif
following script in SQL Server Management Studio window.DECLARE @Var1 FLOAT; DECLARE @Var2 FLOAT; SET @Var1 = 1; SET @Var2 = 0;
Divide By Zero Error Encountered In Stored Procedure
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 http://stackoverflow.com/questions/19631017/simple-way-to-prevent-a-divide-by-zero-error-in-sql 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 FunctionHere is the SQL script with NullIf FunctionDECLARE @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 http://blog.sqlauthority.com/2016/08/27/sql-server-fix-error-8134-divide-zero-error-encountered/ to the entire result set to be NULL instead of an error.Method 2: Use Case StatementHere is the SQL script with CASE StatementDECLARE @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 (http://blog.sqlauthority.com) Tags: SQL Error Messages, SQL Server330Related Articles SQL SERVER - Performance Comparison - INSERT TOP (N) INTO Table - Using Top with INSERT March 3, 2010Pinal Dave SQL SERVER - Unable to Bring SQL Cluster Resource Online - Online Pending and then Failed August 14, 2015Pinal Dave SQL SERVER - 2005 - SSMS - View/Send Query Results to Text/Grid/Files July 19, 2007Pinal Dave 5 comments. Leave new subbu444 August 27, 2016 10:27 amHi,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/
OK, some of the data I am using isnt playing nicely and once in a while I get Divide by zero error encountered. Divide by zero error encountered. Msg 8134, Level 16, State 1, Line 2 To avoid this I am using a CASE as: DECLARE https://ask.sqlservercentral.com/questions/22402/best-way-to-avoid-divide-by-zero.html @Int1 AS INT , @Int2 AS INT SET @Int1 = 6 SET @Int2 = 2 SELECT @Int1 / @Int2 SET @Int1 = 6 SET @Int2 = 0 SELECT @Int1 / CASE WHEN @Int2 = 0 THEN 1 ELSE @Int2 END is there a better way of getting the same results but without littering by nice tidy SELECT statements with hundreds of CASE WHEN ... THEN ... ELSE ... END's?? Am I missing some super useful function that does this in divide by one line? [edit]OK, thanks for the answers so far. I dont want to lose rows where it is zero so, I cant accept a NULL in its place or to filter out those rows with a WHERE @int20 I simply want to replace a 0 value with a 1 value... more ▼ 0 total comments 973 characters / 178 words asked Oct 14, 2010 at 08:54 AM in Default Fatherjack ♦♦ 43.7k ● 79 ● 98 ● 117 edited Oct 14, divide by zero 2010 at 11:03 AM add new comment (comments are locked) 10|1200 characters needed characters left ▼ Everyone Moderators Original poster and moderators Other... Viewable by all users 4 answers: sort voted first ▼ oldest newest voted first 0 You can use NULLIF if you want to return null when division by zero: SELECT @int1 / NULLIF(@int2,0) -- returns NULL or SELECT @int1 / ISNULL(NULLIF(@int2,0),1) -- returns @int1 The CASE statement is probably much faster then the second option, but you can test the performance more ▼ 11 total comments 288 characters / 46 words answered Oct 14, 2010 at 09:04 AM Håkan Winther 16.5k ● 36 ● 45 ● 57 edited Oct 14, 2010 at 09:07 AM Always though about CASE on this one. Good to know about NULLIF Oct 14, 2010 at 09:07 AM ozamora Håkan Winther Nope, the case statement is not any faster because NULLIF is the case statememt. The actuall NULLIF function definition is simply a case statement under the hood, but it has this nice compact form :) nullif (@a, @b) means case when @a = @b then null else @a end Oct 14, 2010 at 09:50 AM Oleg @Håkan Winther Please accept my apologies, It looks like I cannot see straight today, so I did not notice isnull. I do believe though that in this situation the case is going to be only very-very-slightly faster. Oct 14, 2010 at 11:07 AM Oleg @Håk