Ques. on Decimal datatype in sp

[batteam]

I have code in an sp (see below) that interrogates the field 'emvalue' that is a decimal datatype in my SQL table. In my 'Case' stmt in my sp, I want to convert it to a var character field and depending on the value of 'emvalue', put *'s in the field [StarCount]. However, I'm getting the error: "arithmetic overflow converting numeric to data type numberic" message. This error ONLY occurs when a number in my criteria does NOT have a place holder in the tens column. Specifically, the number 9.00 does not work, but the number 10.00 does work. Does the decimal datatype require a number placeholder in the tens column? Or ??


CAUSES ERROR:
Case
When (Cast(emvalue As varChar(10)) Between 9.00 and 134.99) then '*'
End AS [StarCount]


Does NOT CAUSE ERROR:
Case
When (Cast(emvalue As varChar(10)) Between 10.00 and 134.99) then '*'

End AS [StarCount]

Thanks in advance for any help you can offer.

 

[ProdAir]

are you doing the cast before doing the condition (this is what it looks like from your statement above)? I'm not sure why you would need to convert it to a character first before comparing the values...

 

[batteam]

ProdAir: thanks for your response.

I attempted a few different methods (different syntax) to do the cast before the Case stmt but none seemed to work.

Also, I need to convert the decimal field to character so it will to accept the '*' character; when I didn't do any cast/convert, I received a data type conversion error - "...cannot convert to data type numeric..." when that particular Case stmt was run.

Anyway, after your response I went back one more time and put the cast stmt B4 my Case and this time it worked. I don't know what I was doing wrong before, but I had spent most of the day on it B4 I submitted my post. But all seems to be working now.

Again, thanks for your time in offering me assistance.