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

x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 682

# Converting to decimal results to overflow...

I have a stored procedure with this select statement:

select
from      SubscriberQuestionnaire
where      question_id=@question_id

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?

How?
Thanks for the time.
0
gm395
• 2
• 2
1 Solution

Commented:
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
or

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

Author 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?
0

Commented:
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.
0

Author Commented:
Thanks, for all the information
0

## Featured Post

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