Figure is rounding up - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Figure is rounding up

Author

Topic

Pinto
Aged Yak Warrior

590 Posts

Posted - 2007-03-13 : 10:52:30

I have the following sp which is appending records into my table. However the values appended are being round up eg

SC_PrimaryPupilPrice is 1.5

but when it is inserted into the sql table it is 2

The field in the sql table is numeric.

CREATE PROCEDURE spSM_AddWeeksandMealPrices

@dteWeekEnding datetime

as

INSERT INTO tblSM_Meals
(ML_Id,
ML_WeekEnding,
ML_SchoolNumber,
ML_PupilMealPrice,
ML_AdultMealPrice,
ML_SpillagesMealPrice,
ML_AdultRechargeMealPrice,
ML_ReservedMealPrice)

select convert(varchar,@dteWeekEnding ,103) + '*' + cast(SC_SchoolNumber as varchar(10)) , convert(datetime,@dteWeekEnding ,106),
SC_SchoolNumber,
SC_PrimaryPupilPrice,
SC_PrimaryAdultPrice,
SC_PrimaryPupilPrice,
SC_PrimaryAdultPrice,
SC_PrimaryAdultPrice
from tblSM_Schools
GO

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-03-13 : 10:58:30

What is the data type, precision and scale for ML_PupilMealPrice field?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Pinto
Aged Yak Warrior

590 Posts

Posted - 2007-03-13 : 11:01:10

ML_PupilMealPrice is numeric
SC_PrimaryPupilprice is float

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-03-13 : 11:05:45

Yes. That is okay, but what is the scale for numeric column.

sp_help 'table_name'

If it is 0 which I guess it is, then that's the root cause of your problem. Change it to allow desired number of decimal places like (25,6) and then try the UPDATE statement.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Pinto
Aged Yak Warrior

590 Posts

Posted - 2007-03-13 : 11:09:52

0 - I swear I tried to change it to 2 and it reverted back to 0. It has let me change it now and it works. Thank you.
I thought I had to do that and when it intially wouldn't change I thought I had another problem.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources