gm395
asked on
Converting to decimal results to overflow...
I have a stored procedure with this select statement:
select
count(chosen_answer) as sum_chosen_answer,
chosen_answer,
convert(decimal (5, 2),(count(chosen_answer)/@
@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?
How?
Thanks for the time.
select
count(chosen_answer) as sum_chosen_answer,
chosen_answer,
convert(decimal (5, 2),(count(chosen_answer)/@
@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?
How?
Thanks for the time.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
ASKER
Thanks, for all the information
ASKER
Additionally, is it better to use convert or CAST AS?