Converting to decimal results to overflow...

Posted on 2003-03-30
Medium Priority
Last Modified: 2011-10-03
I have a stored procedure with this select statement:

     count(chosen_answer) as sum_chosen_answer,
     convert(decimal (5, 2),(count(chosen_answer)/@num_of_answers)*100) as percentage,
     @num_of_answers as num_of_answers
from      SubscriberQuestionnaire
where      question_id=@question_id
group      by chosen_answer

This returns the results of a questionnaire. Lately for some reason I get an overflow error with this decimal (5,2). If I increase it to (7,2) it works fine.

I read in BOL that casting/converting from INTEGER to DECIMAL may create this. It says:

"Converting from int, smallint, tinyint, float, real, money, or smallmoney to either decimal or numeric can result in overflow.

By default, SQL Server uses rounding when converting a number to a decimal or numeric value with a lower precision and scale. However, if the SET ARITHABORT option is ON, SQL Server raises an error when overflow occurs. Loss of only precision and scale is not sufficient to raise an error."

The SET ARITHABORT ON does nothing in my case.

As this conversion results in a percentage, should I use real/float instead, and CAST AS instead of convert?

Thanks for the time.
Question by:gm395
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
LVL 18

Accepted Solution

nigelrivett earned 200 total points
ID: 8233964
First of all you need to convert the value before the divide otherwise it will truncate to an integer.
select convert(decimal(5,2), (5/2)*100)
will give 200.00
select ((convert(decimal(5,2), 5)/2)*100)
will give 250

5/2 = 2, 5.0/2 = 2.5.

You should always see a .00 as the percetage the way you have it.
either code
(convert(decimal (5, 2),count(chosen_answer)/@num_of_answers)*100)
convert(decimal (5, 2),(count(chosen_answer)*1.0/@num_of_answers)*100)

Your error is probably due to the result value being too big - i.e. greater than 999.99.

Author Comment

ID: 8234129
Thanks for that. I need more help though, as to whether to keep it decimal, or make it float/real? What do you think?

Additionally, is it better to use convert or CAST AS?
LVL 18

Expert Comment

ID: 8234620
decimal. Float is approximate and can cause problems.
Make sure you are working to the precision you need though.

Most of the time it makes no difference whether you use cast or convert.
It prefer convert but cast is ansi standard and new. Cast doesn't allow date styles so you will have to you convert sometimes.

Author Comment

ID: 8237363
Thanks, for all the information

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question