Having trouble w/ Decimal field

11 Feb 2006 4:56 AM

Sam

Hi,

I have a decimal field with precision 3 and scale 1. This field will have
values ranging from 0.5 to 10.

When enter new values into the table using the following stored procedure,
my decimal values get rounded up. If I enter values directly into the table,
everything is fine. Do I need to define the precision and scale of the
decimal field in my stored procedure? What could be causing this odd behavior?

Here's my stored procedure...
ALTER PROCEDURE dbo.spTalentReleaseNew
    (
        @EmployeeID smallint,
        @JobName varchar(200),
        @JobDate smalldatetime,
        @DealID int,
        @JobLength decimal,
        @Producer smallint,
        @TalentAgencyName varchar(200) = null,
        @TalentID int,
        @TalentRate smallmoney,
        @LocationRate smallmoney,
        @MakeUpRate smallmoney,
        @FoodStylistRate smallmoney,
        @LastUpdateTimeStamp datetime
    )
AS

    INSERT INTO tblTalentRelease
                          (EmployeeID, JobName, JobDate, DealID, JobLength,
TalentAgencyName, TalentID, TalentRate, LocationRate, MakeUpRate,
FoodStylistRate,
                          LastUpdateTimeStamp, LastUpdatedBy, Producer)
    VALUES     (@EmployeeID, @JobName, @JobDate, @DealID, @JobLength,
@TalentAgencyName, @TalentID, @TalentRate, @LocationRate, @MakeUpRate,
                          @FoodStylistRate, @LastUpdateTimeStamp, @EmployeeID,
@Producer)

--
Thanks,

Sam

11 Feb 2006 5:44 AM

Dave Frommer

Show quote

"Sam" <S**@discussions.microsoft.com> wrote in message
news:9046BD31-2134-4C91-BB87-0942C60E854A@microsoft.com...
> Hi,
>
> I have a decimal field with precision 3 and scale 1. This field will have
> values ranging from 0.5 to 10.
>
> When enter new values into the table using the following stored procedure,
> my decimal values get rounded up. If I enter values directly into the
> table,
> everything is fine. Do I need to define the precision and scale of the
> decimal field in my stored procedure? What could be causing this odd
> behavior?
>

Yes, If you do not specify this it will default to no decimal places

Show quote

> Here's my stored procedure...
> ALTER PROCEDURE dbo.spTalentReleaseNew
> (
> @EmployeeID smallint,
> @JobName varchar(200),
> @JobDate smalldatetime,
> @DealID int,
> @JobLength decimal,
> @Producer smallint,
> @TalentAgencyName varchar(200) = null,
> @TalentID int,
> @TalentRate smallmoney,
> @LocationRate smallmoney,
> @MakeUpRate smallmoney,
> @FoodStylistRate smallmoney,
> @LastUpdateTimeStamp datetime
> )
> AS
>
> INSERT INTO tblTalentRelease
>                       (EmployeeID, JobName, JobDate, DealID, JobLength,
> TalentAgencyName, TalentID, TalentRate, LocationRate, MakeUpRate,
> FoodStylistRate,
>                       LastUpdateTimeStamp, LastUpdatedBy, Producer)
> VALUES     (@EmployeeID, @JobName, @JobDate, @DealID, @JobLength,
> @TalentAgencyName, @TalentID, @TalentRate, @LocationRate, @MakeUpRate,
>                       @FoodStylistRate, @LastUpdateTimeStamp, @EmployeeID,
> @Producer)
> --
> Thanks,
>
> Sam

11 Feb 2006 6:06 AM

Louis Davidson

Try it :)

declare @value decimal

set @value = 10.323232

select @value

---------------------------------------

10

Seriously, you should define your parameters in the same datatype that you
want to store in almost all cases. Not specifying type is asking for SQL
Server to guess for you. I am guessing that your JobName column is
varchar(200) right? Not 300, or unicode. Same principals apply.

-
----------------------------------------------------------------------------
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)

Show quote

"Sam" <S**@discussions.microsoft.com> wrote in message
news:9046BD31-2134-4C91-BB87-0942C60E854A@microsoft.com...
> Hi,
>
> I have a decimal field with precision 3 and scale 1. This field will have
> values ranging from 0.5 to 10.
>
> When enter new values into the table using the following stored procedure,
> my decimal values get rounded up. If I enter values directly into the
> table,
> everything is fine. Do I need to define the precision and scale of the
> decimal field in my stored procedure? What could be causing this odd
> behavior?
>
> Here's my stored procedure...
> ALTER PROCEDURE dbo.spTalentReleaseNew
> (
> @EmployeeID smallint,
> @JobName varchar(200),
> @JobDate smalldatetime,
> @DealID int,
> @JobLength decimal,
> @Producer smallint,
> @TalentAgencyName varchar(200) = null,
> @TalentID int,
> @TalentRate smallmoney,
> @LocationRate smallmoney,
> @MakeUpRate smallmoney,
> @FoodStylistRate smallmoney,
> @LastUpdateTimeStamp datetime
> )
> AS
>
> INSERT INTO tblTalentRelease
>                       (EmployeeID, JobName, JobDate, DealID, JobLength,
> TalentAgencyName, TalentID, TalentRate, LocationRate, MakeUpRate,
> FoodStylistRate,
>                       LastUpdateTimeStamp, LastUpdatedBy, Producer)
> VALUES     (@EmployeeID, @JobName, @JobDate, @DealID, @JobLength,
> @TalentAgencyName, @TalentID, @TalentRate, @LocationRate, @MakeUpRate,
>                       @FoodStylistRate, @LastUpdateTimeStamp, @EmployeeID,
> @Producer)
> --
> Thanks,
>
> Sam