Hi,

In my user defined function I am using code like this to get a number with 2 decimal places:

if @reportCurrency=1
   begin
     set @calculatedRate = @fltAdRate + ISNULL(@fltPosRate,0)
   end

if @reportCurrency=2
   begin
     set @calculatedRate = (@fltAdRate + ISNULL(@fltPosRate,0))- (@fltAdRate + ISNULL(@fltPosRate,0)) * IsNull(@agencyDiscount,15) / 100
   end
if @reportCurrency=3
   begin
     set @calculatedRate = (@fltAdRate + ISNULL(@fltPosRate,0))-(@fltAdRate + ISNULL(@fltPosRate,0)) * (IsNull(@agencyDiscount,15) / 100)
     set @calculatedRate = (@calculatedRate)- (@calculatedRate) * (IsNull(@cashDiscount,0) /100)
end

Then I cast the @calculatedRate

Set @returnVal = ISNULL(CAST(@calculatedRate AS DECIMAL(15,2)), -1)

if @returnVal = -1.00
     SET @returnVal = CAST (@returnVal AS DECIMAL(15,0))


RETURN @returnVal

The problem is that it rounds the number. Example:

Gross amount $2415 net down 15% = $2052.75 Not $2053.00

AS you see the .75 cents is rounded to 2053.00. I would like to keep the .75 cents like $2052.75

I would appreciate your help.

Cheers

This is a simpler example of my problem. I am using query analyser to test.

It seems that if I change the return type of the user defined function to float and decalre all the variables to be float it gives the right result.

The only problem now is when I test for null. If it is null I have to return -1.

But it is returning -1.0

How can get rid of the .0 and return just -1. I have tried to cast to int but to no avail.

DECLARE @calculatedRate float
DECLARE @returnVal float

DECLARE @fltAdRate float
SET @fltAdRate = 2415.00

DECLARE @fltPosRate float
SET @fltPosRate = NULL

DECLARE @cashDiscount float
SET @cashDiscount = 0.0

DECLARE @agencyDiscount float
SET @agencyDiscount = 15.0

set @calculatedRate = (@fltAdRate + ISNULL(@fltPosRate,0))- (@fltAdRate + ISNULL(@fltPosRate,0)) * @agencyDiscount / 100

Set @returnVal = ISNULL(CAST(@calculatedRate AS DECIMAL(15,2)), -1)

SET @returnVal = -1

if @returnVal = -1 or @returnVal = -1.0 or @returnVal = -1.00
     --SET @returnVal = CAST (@returnVal AS DECIMAL(15,0))
    SET @returnVal = CAST (@returnVal AS int)

select @returnVal as returnVal