# Thread: Data type Decimal workd different in sproc vs Query Analyzer

1. Registered User
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. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
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. Registered User
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. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579