decimal

Question

decimal

Mike Levan

SSChampion

Points: 14201
More actions
September 20, 2007 at 11:44 am

#121707

Hi
Recently I have changed an int column to decimal(2,1), when I entered 5.5 from my application isee it as 6, what is the reason? Looks like a rounding problem in sql server

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Antares686 SSC Guru Points: 125444 More actions · Answer 1

Do you use Stored Procedures to access the data. If so and you used an Output variable, did you change the variable type there as well. Usually that type of thing is the issue. Or could be that if is an SP Output variable and you fixed there your app may have the return defined as an INT output and the parameter needs to be fixed in the app code.

Mike Levan SSChampion Points: 14201 More actions · Answer 2

yes i do tht through store proc, and also changed tht in my proc. Do u think there may be something from the app side

David McFarland SSChampion Points: 11815 More actions · Answer 3

While Antares686 is likely correct as to your specific problem, the way I attack these types of things is start at the app to find out what populates the column in the first place. Depending on the dev methodology used at your company, it could be anything from inline code to a stored procedure call. That will tell you what needs to be fixed.

Mike Levan SSChampion Points: 14201 More actions · Answer 4

I checked in the app, it looks very simple in tht, the app just calls my proc and when it asks for parameteri eneter 5.5, right after exucuting it displays 6 in the result. kind of weired for me, pls get me a way to resolve

Antares686 SSC Guru Points: 125444 More actions · Answer 5

How does you app code call your Proc? Is it by building a string from it or do you use Paremeter objects. If the later then check the object for the item and make sure Interger is changed to Decimal. But keep in mind with Decimal you then have to define Precision and NumericScale for it to work properly.

This exampl is from a old ASP (not .NET) app in this case the field is Decimal(10,0)

cmdSQL.Parameters.Append cmdFCDB.CreateParameter("Home_Phone", adDecimal, adParamInput, , null)

cmdSQL.Parameters("Home_Phone").Precision = 10

cmdSQL.Parameters("Home_Phone").NumericScale = 0

cmdSQL.Parameters("Home_Phone") = StripPhone(arContact(3))

Example in ASP.NET (C#) in this case the field is Decimal(9,4)

cmdSQL.Parameters.Add(

new SqlParameter("@Zip",SqlDbType.Decimal,9,ParameterDirection.Input,false,9,4,"Zip",DataRowVersion.Current,Prop.Zip));

Not 9,4 after false, that is Precision and NumericScale

David McFarland SSChampion Points: 11815 More actions · Answer 6

Have you checked how it's stored in the table? If it's 5.5 there, the problem is in formatting the value in the app itself. If it's 6 in there, the problem is likely in the method that you use to populate the table.