0

I have a store d procedure that takes a decimal from a datagrid and places it into a database. for example my grid(and the procedure) is putting in .412 but in the database it comes out as 0.412000000476837. why is this and is there a way I can keep the decimal places down to just what I entered? the database field is a float. Th proc is just a simple update procedure.

    UPDATE smf_CustomerPartPriceBreaks_TEST
SET UnitRackPrice = @rackadjustment,
    UnitBulkPrice = @bulkadjustment
FROM [SMFDATA].[dbo].[smf_CustomerPartPriceBreaks_TEST]

join smf_CustomerParts on smf_CustomerParts.QuoteID = smf_CustomerPartPriceBreaks_TEST.QuoteId WHERE RTRIM(CustomerPartNumb) = @part

the @rackadjustment and @bulkadjustment parameters are the simple decimals i am entering the numbers for.

rigamonk
  • 1,179
  • 2
  • 17
  • 45
  • Lots of good discussion on floating point vs decimal in [this question](http://stackoverflow.com/questions/588004/is-floating-point-math-broken) - it's pretty much true for any language, including SQL. – Joe Enos Aug 05 '14 at 21:36

1 Answers1

0

In Your Table set the datatype as DECIMAL(10,2)

user3842306
  • 56
  • 4
  • You should be able to adjust the data in it by going to Tools >> Options >> Designers >> Un Check Prevent changes that require table re-creation. Changing the data type will not loose data as long as that column is already set to a decimal. Sql will automatically adjust the decimal places. To Be safe back your database before trying so you are sure you do not loose any data. – user3842306 Aug 05 '14 at 21:39
  • Otherwise when returning the data to your application user SqlDecimal.ToString("#.##") or SqlDecimal.ToString("n2") – user3842306 Aug 05 '14 at 21:41
  • Or cast it in your query. – Andrew Aug 05 '14 at 21:42
  • True Andrew has another good point you could use something like SELECT CONVERT(DECIMAL(10,2),10000) – user3842306 Aug 05 '14 at 21:43
  • i saved it as decimal (10,4) but i am getting errors, could it be because of nulls in the database? – rigamonk Aug 05 '14 at 21:48
  • Null would only throw an error if you where trying to receive a null object. could you please provide the error you are receiving ? – user3842306 Aug 05 '14 at 21:49