Handling Divide By Zero Error In Sql Server 2008

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 oracle sql divide by zero company Business Learn more about hiring developers or posting ads with us Stack Overflow divide by zero error encountered excel Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 4.7

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

million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up How to avoid the “divide by zero” error in SQL? up vote 188 down vote favorite 46 I have

Nullif Sql

this error message: Msg 8134, Level 16, State 1, Line 1 Divide by zero error encountered. What is the best way to write SQL code so that I will never see this error message again? I could do either of the following: Add a where clause so that my divisor is never zero Or I could add a case statement, so that there is a special treatment for zero. Is the best way divide by zero error encountered in stored procedure to use a NullIf clause? Is there better way, or how can this be enforced? sql sql-server sql-server-2005 sql-server-2008 share|improve this question edited Jan 6 at 19:50 J.D. 4311525 asked May 14 '09 at 6:06 Henrik Staun Poulsen 4,92331220 4 Perhaps some data validation is in order. –Anthony May 14 '09 at 19:17 add a comment| 15 Answers 15 active oldest votes up vote 352 down vote accepted In order to avoid a "Division by zero" error we have programmed it like this: Select Case when divisor=0 then null Else dividend / divisor End ,,, But here is a much nicer way of doing it: Select dividend / nullif(divisor, 0) ... Now the only problem is to remember the NullIf bit, if I use the "/" key. share|improve this answer edited Dec 20 '12 at 1:04 Community♦ 11 answered May 14 '09 at 6:10 Henrik Staun Poulsen 4,92331220 that's the way I would have solved it. –J. Polfer May 14 '09 at 19:21 4 A much nicer Way of doing it "Select dividend / nullif(divisor, 0) ..." breaks if divisor is NULL. –Anderson Dec 1 '14 at 10:51 add a comment| Did you find this question interesting? Try our newsletter Sign up for our newsletter and get our top new questions delivere

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

Divide By Zero Error Encountered. The Statement Has Been Terminated

is the final part in a four part series that discusses the SQL divide by zero error encountered c# NULL value. I haven't read the first three parts yet, but there is a really cool tip in the fourth part divide by 0 in 128 bit arithmetic netezza on using NULLIF() to prevent divide-by-zero 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 http://stackoverflow.com/questions/861778/how-to-avoid-the-divide-by-zero-error-in-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 https://www.bennadel.com/blog/984-using-nullif-to-prevent-divide-by-zero-errors-in-sql.htm NULL if the two values are the same and can be used 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: