Developing Matt

My Technical Journal

Error converting data type numeric to decimal

leave a comment »

Simple one.  So simple, I shouldn’t have spent so much time trying to figure it out. So simple, I should probably quit anything that boasts of ‘information’ and ‘technology’.’  But alas, my pride is more stubborn than this.

I have an application that is calling a procedure and it has a parameter with a decimal datatype.  This error almost always has to do with your parameter setup in the stored procedure.  I had my parameter sitting at decimal(2,2) and I was passing in a single number: 5.  Now, should this work?  You’d think so.  Precision (the number of digits the object can hold) says, ‘two digits is fine with me’ and scale (number of digits to the right of the decimal point) states ‘hey, two digits after the decimal is GOOD!.’  But you are wrong.

For clarity let’s say my declaration stated decimal(3,2).  This would mean only 3 numbers are allowed in the entire number (precision), and only 2 allowed after the decimal (scale).  This would mean that only a single digit would be allowed prior to the decimal.  So for 3,2 the number 1.55 would work.  12.55 would not.  1.556 would not.  Palomino, Texas would not.  Nor would an orange or a tomato or my older brother who still picks on me for that matter.  So 1.55 would work for the declaration of (3,2).  So what does that say about my declaration of (2,2)?  For 2,2 I could only pass in fractions.  Two numbers total are accepted and of those two numbers, two of them have to be to the right of the decimal.  I was attempting to pass in a single number (5). 

I bumped my precision to 5 so that my parameter stored a parameter of decimal (5,2) and everything returned back to normal.


Written by matt

October 25, 2011 at 1:54 pm

Posted in, T-Sql

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: