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
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.