Ms Sql Server Divide By Zero Error

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 divide by zero error encountered in sql Business Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs oracle sql divide by zero Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 6.2 million programmers, just divide by zero error encountered excel like you, helping each other. Join them; it only takes a 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

Msg 8134 Level 16 State 1 Line 1 Divide By Zero Error Encountered

which used to cause a Divide By Zero exception, I've wrapped it in a CASE 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.6k43873 1 It depends on how you wan't your query and result nullif sql to behave when AttTotal is 0 or NULL, so there is no general way of doing it. I'd say your query is just 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 27 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.3k42940 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

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, divide by zero error encountered in stored procedure State 1, Line 2 To avoid this I am using a CASE as:

Divide By Zero Error Encountered. The Statement Has Been Terminated

DECLARE @Int1 AS INT , @Int2 AS INT SET @Int1 = 6 SET @Int2 = 2 SELECT @Int1 / @Int2

Divide By 0 In 128 Bit Arithmetic Netezza

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 http://stackoverflow.com/questions/19631017/simple-way-to-prevent-a-divide-by-zero-error-in-sql 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 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 https://ask.sqlservercentral.com/questions/22402/best-way-to-avoid-divide-by-zero.html 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, 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

Javier Julio Using NULLIF() To Prevent Divide-By-Zero Errors In SQL By Ben Nadel on October 3, 2007 Tags: SQL Boyan Kostadinov just sent me a cool link to an article that is the final part https://www.bennadel.com/blog/984-using-nullif-to-prevent-divide-by-zero-errors-in-sql.htm in a four part series that discusses the SQL NULL value. I haven't read the first three parts yet, but there is a really cool tip in the fourth part on using NULLIF() to prevent divide-by-zero https://msdn.microsoft.com/en-us/library/ms184341.aspx errors in a SQL call.The idea here is that, as with any other form of math that I know of, you cannot divide by zero in a SQL call. Therefore, running this code:SELECT( 45 / 0 ) AS value;... results in a SQL error being thrown:Error Executing Database Query. [Macromedia] [SQLServer JDBC Driver] [SQLServer] Divide by zero error encountered.To prevent this sort of error from being thrown, author Hugo Kornelis suggests using a NULLIF() in the divisor of the equation. NULLIF() takes two arguments and returns NULL if the two values are the same and can be used divide by zero to turn the divisor from a zero into a NULL which, in turn, will force the entire equation to become NULL. Therefore, running this code:SELECT( 45 / NULLIF( 0, 0 ) ) AS value;[ #qDivision.value# ]... we get the following output:[ ]Here, the NULLIF( 0, 0 ) returns NULL since zero is equal to zero, which gets the SQL statement to return NULL, which gets ColdFusion to show an empty string. This is a seemingly pointless example since both zero values are hard coded, but imagine if this were a user-entered value, or even better yet, a SQL aggregate or other calculated value (such as might be used in a report or data mining exercise).Now, let's say you want to take this one step further and provide a default value for the equation if NULL is encountered (A default value, though not entirely accurate might make your consuming code more compact as it won't have to deal with exception cases). To set a default value, we could use the ISNULL() or COALESCE() functions:SELECT(ISNULL((45 / NULLIF( 0, 0 )),0)) AS value;