SQL Server 2005 table inadvertently rounding Decimal fields


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 4 of 4

Thread: SQL Server 2005 table inadvertently rounding Decimal fields

Hybrid View

  1. #1
    Join Date
    Jan 2004
    Posts
    60

    SQL Server 2005 table inadvertently rounding Decimal fields

    I have a table that is storing decimal values that are formatted as Decimal(11,2) and Decimal(15,9). An SP feeds the table via updates or inserts. The parameters are all coming in correctly; however, it appears the table is rounding to the nearest whole number. Any idea on what can be causing this?

  2. #2
    Join Date
    Feb 2008
    Posts
    162
    What are the parameter types? How are you inserting the values into the table?
    It's not what you don't know that gets you in trouble. It's what you know that just ain't so. -Mark Twain

  3. #3
    Join Date
    Jan 2004
    Posts
    60
    Here's the SP:

    CREATE PROCEDURE [dbo].[AddCheckDetail](@CHKDID VARCHAR(22),
    @CHKID BIGINT,
    @PRPD VARCHAR(12),
    @ORG$ DECIMAL(11,2),
    @DCML DECIMAL(10,9),
    @GRS$ DECIMAL(11,2),
    @TAX$ DECIMAL(11,2),
    @OTH$ DECIMAL(11,2),
    @NET$ DECIMAL(11,2),
    @ROIM DECIMAL(15,9),
    @ROI3 DECIMAL(15,9),
    @ROIY DECIMAL(15,9),
    @ROIL DECIMAL(15,9),
    @VERIFIED BIT)
    AS

    BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here

    IF EXISTS(SELECT * FROM CheckDetail WHERE CheckDetailID = @CHKDID)
    BEGIN
    UPDATE CheckDetail SET CheckDetailID = @CHKDID,
    CheckID = @CHKID,
    PropertyPeriodID = @PRPD,
    OrigInvestmentAmt = @ORG$,
    RevDistInterest = @DCML,
    GrossAmt = @GRS$,
    TaxAmt = @TAX$,
    OtherAmt = @OTH$,
    NetAmt = @NET$,
    MonthROI = @ROIM,
    ThreeMonthROI = @ROI3,
    YTDROI = @ROIY,
    LifetimeROI = @ROIL,
    Verified = @VERIFIED
    WHERE CheckDetailID = @CHKDID
    END
    ELSE
    BEGIN
    INSERT INTO CheckDetail(CheckDetailID,
    CheckID,
    PropertyPeriodID,
    OrigInvestmentAmt,
    RevDistInterest,
    GrossAmt,
    TaxAmt,
    OtherAmt,
    NetAmt,
    MonthROI,
    ThreeMonthROI,
    YTDROI,
    LifeTimeROI,
    Verified)
    VALUES(@CHKDID,
    @CHKID,
    @PRPD,
    @ORG$,
    @DCML,
    @GRS$,
    @TAX$,
    @OTH$,
    @NET$,
    @ROIM,
    @ROI3,
    @ROIY,
    @ROIL,
    @VERIFIED)
    END
    END

  4. #4
    Join Date
    Feb 2008
    Posts
    162
    And the table column types are the same? Looks like it should be working. I was just thinking that if you were doing any kind of calculations with int values then you have to make sure you cast one of them to decimal or it will round the result to int. ie – 4 / 3 = 1, but 4 / 3.0 = 1.333333.
    Last edited by Slope; 07-21-2008 at 07:05 PM.
    It's not what you don't know that gets you in trouble. It's what you know that just ain't so. -Mark Twain

Similar Threads

  1. SQL server 2005 - No windows connections
    By hospea in forum Database
    Replies: 0
    Last Post: 08-30-2007, 04:40 AM
  2. Replies: 0
    Last Post: 11-01-2006, 12:07 PM
  3. Syntax Error Updating SQL Server Table
    By Ralf in forum Database
    Replies: 26
    Last Post: 08-08-2002, 09:58 AM
  4. Access link table in SQL Server?
    By John Wood in forum Database
    Replies: 1
    Last Post: 10-30-2000, 11:32 AM
  5. SQL server administrative knowledge
    By Becky in forum Database
    Replies: 1
    Last Post: 07-11-2000, 04:27 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center