Why are my decimals being rounded? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

Why are my decimals being rounded?

Author

Topic

bwol
Starting Member

19 Posts

Posted - 2008-07-08 : 14:49:35

I have an MSSQL 2000 database table with a field named "Dues", which has a data type of decimal (length of 5, precision of 5 and scale of two).

When I try to use the stored procedure below to insert a new row into this table, the Dues value is stored in my table as an Integer, instead of a decimal (even though I know that the value being passed in is a decimal and not an integer).

What can I do to prevent this rounding from occurring???

Any suggestions are much appreciated!

CREATE PROCEDURE AddMonthlyDuesLine

(
@ReportID int,
@SSN varchar(11),
@MemberName varchar(41),
@Dues decimal
)

AS

IF
(SELECT COUNT(*)
FROM MonthlyDues
WHERE SSN = @SSN AND ReportID = @ReportID) > 0

UPDATE MonthlyDues
SET Dues = Dues + @Dues
WHERE SSN = @SSN AND ReportID = @ReportID

ELSE

INSERT INTO MonthlyDues (ReportID, SSN, MemberName, Dues)
VALUES (@ReportID, @SSN, @MemberName, @Dues)

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-07-08 : 15:05:13

You need to set the scale and precision when you declare the decimal variable

declare @d decimal(3,2)
set @d = 1.1
select @d
--returns 1.10
go
declare @d decimal
set @d = 1.1
select @d
--return 1

Be One with the Optimizer
TG

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2008-07-08 : 15:06:01

(
@ReportID int,
@SSN varchar(11),
@MemberName varchar(41),
@Dues decimal(5,2)
)

Jim

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-08 : 15:16:55

And replace

IF (SELECT COUNT(*) FROM MonthlyDues WHERE SSN = @SSN AND ReportID = @ReportID) > 0

with

IF EXISTS (SELECT * FROM MonthlyDues WHERE SSN = @SSN AND ReportID = @ReportID)

because it will most probable execute faster.

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources