November 7, 2006 at 1:58 pm
Hello,
I have a stored procedure that I use that works just fine on SQL 2K. However, I started running into the "Error converting data type varchar to numeric" on a specfic line in the stored procedure when I moved the database to SQL 2K5 with SQL 2K compatibility.
The line that is erroring out is
ISNULL(CONVERT(decimal(15,2),pre.C_CLAIMTOT),'') 'ClaimCharges'
The column C_CLAIMTOT is on 'Money' data type. Not sure why the decimal conversion is giving me this error.
Any ideas? Also, what is the best way to handle this in SQL2K5?
Thanks,
CK
November 7, 2006 at 3:15 pm
How are C_CLAIMTOT values being stored? Are they stored with the money symbol ($, etc), are they stored with a comma ($1,000). If so, I'm pretty sure that it's being considered a varchar for the conversion.
One thing to note....when you have an error in QA and double click on it...the line it highlights may NOT be the actual line with the error. The error could actually be in a line near that one.
-SQLBill
November 7, 2006 at 3:33 pm
Thanks for your response SQLBill. Only amounts are stored in the C_CLAIMTOT field. No money symbols. Some example values are 546.91, 2110.9, 2143 etc.
You are right about the line number that is displayed as error and the line on which the actual error is. I basically commented out the ISNULL(CONVERT(decimal(15,2),pre.C_CLAIMTOT),'') 'ClaimCharges' and replaced it with ISNULL(STR(pre.C_CLAIMTOT,15,2),'') 'ClaimCharges' to get this to work.
But, it is still puzzling me as to why I am getting a varchar to numeric conversion error on ISNULL(CONVERT(decimal(15,2),pre.C_CLAIMTOT),'') 'ClaimCharges' statment.
November 7, 2006 at 11:46 pm
That line will give you an error in SQL 2000 as well. The error is caused by the IsNull() function, and is not related to your data type conversion. Empty string is varchar whereas your numerical value is decimal. When using IsNull, the substitute value has to be the same datatype or explicitly convertible to the other value's datatype. A varchar value is not explicitly convertible to a numerical value.
Instead of using the Str() function, another option is to use: ISNULL(CONVERT(decimal(15,2),pre.C_CLAIMTOT), 0)
November 8, 2006 at 8:22 am
That sure did work Robert. Thanks!!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy