Results 1 to 4 of 4
  1. #1
    Join Date
    May 2003
    Location
    Atlanta, GA
    Posts
    187

    Unanswered: Data type Decimal workd different in sproc vs Query Analyzer

    OK, maybe I need to understand how it works behind the scene but a puzzle to me now.

    OK, in my sproc I have this code which returns 3.010000
    CREATE PROCEDURE Util_TACSImportsTest
    (
    @DepositDate datetime,
    @SequenceNumber int
    )
    AS

    DECLARE @GrossAmount money,
    @NetAmount money,
    @FeeDiffPercent decimal(18,6),
    @FeePercent decimal(18,6)

    SET NOCOUNT ON

    -- Get Gross Amount
    SELECT @GrossAmount = SUM(CommissionAmount)
    FROM tblTACSImport
    WHERE DepositDate = @DepositDate
    AND SequenceNumber = @SequenceNumber

    -- Get Net Amount
    SELECT @NetAmount = Amount
    FROM tblCheckBatch
    WHERE DepositDate = @DepositDate
    AND SequenceNumber = @SequenceNumber

    SELECT @FeeDiffPercent = @NetAmount / @GrossAmount
    SELECT @FeePercent = 100 - (@FeeDiffPercent * 100)

    IF @FeePercent > 3
    BEGIN
    SELECT @FeePercent
    RAISERROR ('Fee percent can not be greater then 3 percent. Please fix and try to reimport', 16, 1)
    RETURN
    END

    RETURN
    GO

    My values come out to be:
    @NetAmount = 25794.80
    @GrossAmount = 26592.92

    If I run this in Query Analyzer I get different results:
    select 25794.80 / 26592.92 = 0.9699875004
    select 100 - (0.9699875004 * 100) = 3.0012499600

    which is going to be 3.00 and not 3.01

    What am I doing wrong?

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    You declare @FeePercent to be DECIMAL (18, 6) which means that the value stored should be 3.001250 after rounding. At least using my math, that is larger than 3. Where's the surprise?

    -PatP

  3. #3
    Join Date
    May 2003
    Location
    Atlanta, GA
    Posts
    187
    Thats not my question. I know thats larger and I changed my code to just 2 decimal places. My question is why does the sproc return 3.01 and in query analyzer it comes out to 3.00

    Any help?

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    This code works the same in a stored procedure or when executed as a query for me. It produces 3.010000 in both cases.

    -PatP

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •