Divided By Zero Error Encountered 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 msg 8134 level 16 state 1 line 1 divide by zero error encountered hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask
Divide By Zero Error Encountered In Stored Procedure
Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join
Divide By Zero Error Encountered In Sql Server 2012
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 this error message: Msg 8134, Level 16, State 1, Line 1 Divide
Divide By Zero Error Encountered Sql Server 2008
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 to use a NullIf clause? Is there better way, or how can this be enforced? sql sql-server tsql divide by zero error encountered sql-server-2005 sql-server-2008 share|improve this question edited Jan 6 at 19:50 Hooper 4241525 asked May 14 '09 at 6:06 Henrik Staun Poulsen 4,89331220 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 350 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,89331220 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| up vote 87 down vote In case you want to return zero, in case a zero devision would happen, you can use: SELECT COALESCE(dividend / NULLIF(divisor,0), 0) FROM sometable For every divisor that is zero, you will get a zero in the result set. share|improve this answer edited Jan 15 '13 at 19:41 Peter Mortensen 10.2k1369107
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 excel is the final part in a four part series that discusses the SQL oracle sql divide by zero NULL value. I haven't read the first three parts yet, but there is a really cool tip in the fourth part sql nullif 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 http://stackoverflow.com/questions/861778/how-to-avoid-the-divide-by-zero-error-in-sql SQL call. Therefore, running this code:
by Zero Using NULLIF Posted on April 5, 2012 by Derek Dieter 1 4 « A Query Method Faster Than BETWEENCreating Unique Random Numbers » Anytime we are dividing we need to think of the divide by zero scenario. Even if you think you will never encounter this with your result set, http://sqlserverplanet.com/tsql/overcome-divide-by-zero-using-nullif it's advisable to guard against it because when divide by zero is encountered, an error is thrown. http://www.peachpit.com/blogs/blog.aspx?uk=Avoiding-division-by-zero-with-NULLIF-Five-SQL-Tips-in-Five-Days-Part-5- The best method I've found to overcome this is by using the NULLIF function. This function takes two parameters and if they are equal, a NULL value is returned. Lets take a look at an example that throws a divide by zero error. DECLARE @iter FLOAT; DECLARE @num FLOAT SET @num = 10; SET @iter = 5; WHILE @iter > -5 BEGIN SELECT @num / @iter by zero SET @iter = @iter - 1 END Running the following query, we see that once the variable @iter becomes zero, we receive an error. So the most elegant way to overcome this is to use NULLIF function and compare @iter to zero. When it does equal zero, it will instead change it to a null. And when dividing anything by NULL will equal a NULL. DECLARE @iter FLOAT; DECLARE @num FLOAT; SET @num = 10; SET @iter = 5; WHILE @iter > by zero error -5 BEGIN SELECT @num / NULLIF(@iter,0); SET @iter = @iter - 1; END This executes without error, however we still receive a null as a result. If you need otherwise, then you may want to wrap the equation in an ISNULL, to return a different value. DECLARE @iter FLOAT; DECLARE @num FLOAT; SET @num = 10; SET @iter = 5; WHILE @iter > -5 BEGIN SELECT ISNULL(@num / NULLIF(@iter,0),@num); SET @iter = @iter - 1; END This will just return the same number you are dividing by, if you encounter a NULL denominator. Filed under TSQL Related Posts: SQL Server For Each Row Next SQL Server Begin Try Concatenate Rows Using Coalesce Alter Index All Tables How to Concatenate SQL Rank SQL Replace Compare Stored Procedure Output by Creating a Test Harness Substring Between Two Characters Post a comment Comments (RSS) Trackback Permalink Click here to cancel reply. Name: Email: Comment: One comment Jiwa 29 Aug 2015 at 8:36 am ( 2012.02.11 08:02 ) : This feeder works great as long as you fololw the directions and complete ALL the steps for setting it up. After programming the current time, you set the times you want them to eat, THEN go back to those times and select 1 rotation or 2, depending on how much you want them to have. The default is 0, so that's why it didn't work for me at first. Now that I have it program
By Chris FehilyJul 25, 2008 Topics: ProductivitySuppose you want to calculate the male–female ratios for various school clubs, but you discover that the following query fails and issues a divide-by-zero error when it tries to calculate ratio for the Lord of the Rings Club, which has no women: SELECT club_id, males, females, males/females AS ratio FROM school_clubs; You can use the function NULLIF to avoid division by zero. NULLIF compares two expressions and returns null if they are equal or the first expression otherwise. Rewrite the query as: SELECT club_id, males, females, males/NULLIF(females, 0) AS ratio FROM school_clubs; Any number divided by NULL gives NULL, and no error is generated. Be sure to look back over my posts this week for more SQL tips. And for more on SQL, be sure to check out my book SQL: Visual QuickStart Guide, 3rd Edition. Share|Share on twitter Share on facebook Share on google Share on email✉ Other Things You Might LikeData at Work: Best practices for creating effective charts and information graphics in Microsoft Excel By Jorge CamõesBook $35.99Speaker Camp Workshop: Learn by Video By Russ Unger, Samantha StarmerDVD $39.99Windows 8: Visual QuickStart Guide By Barrie SosinskyBook $27.99 View Your Cart Account Sign InSearchSearch View Your Cart 👤 Account Sign In Create AccountTopicsAdobe PhotoshopAppleDesignDigital PhotographyDigital VideoGadgetsProductivity Web Design & Development StoreBookseBooksVideoWeb EditionsAuthorsSafariExploreAbout UsAcademic ResourcesAffiliatesArticlesBlogsNewslettersPressUser GroupsAboutAffiliatesContact UsJobsLegal NoticePrivacy PolicyPressPromotionsSite MapWrite for Us © 2016 Pearson Education, Peachpit. All rights reserved. 1301 Sansome Street, San Francisco, CA 94111