Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 682
  • Last Modified:

Converting to decimal results to overflow...

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.
  • 2
  • 2
1 Solution
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.
gm395Author Commented:
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?
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.
gm395Author Commented:
Thanks, for all the information

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now