Hi,

Using SQL 2000

I have a stored proc that returns a value after some calculations.

The value returned should always include 2 decimal places but this is not happening.

Examples of what it is returning:

2975.0
123.456
34.55
10998.796

As you can see I need to do some adjustments so that it always returns the value with 2 decimal places.

I would appreciate if someone could shed some light on this issue. Is it possible to have this level of control in the stored proc?

This is the last bit of the code I am using to return the value:

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)) * @agencyDiscount / 100
   end
if @reportCurrency=3
   begin
     set @calculatedRate = (@fltAdRate + ISNULL(@fltPosRate,0))-(@fltAdRate + ISNULL(@fltPosRate,0)) * (@agencyDiscount / 100)
     set @calculatedRate = (@calculatedRate)- (@calculatedRate) * (@cashDiscount /100)
end

 --Output calculated rate
SELECT @calculatedRate as Rate

Cheers

SELECT CAST(@calculatedRate AS DECIMAL(15,2)) as Rate


--Jeff Moden

Hi Jeff,

Thanks for the reply.

I can see that the 2 means two decimal places what about the 15?

Cheers

Hi Jeff,

I have tried the CAST you showed above and it works fine when I use a SELECT statement to return the value.

However, since I am using a user defined function I cannot use the SELECT statement to return a value. I have to use the RETURN statement.

That is what returns the value:

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

This is how I am testing it in query analyser:

select dbo.GetRate( 40, 423, 1, 1, 540, 0, 1)

And get

3725.0

Could you please shed some light?

Cheers

The code for the return value returns two decimal places just fine on my box... without seeing the GetRate code, I can't tell what else may be wrong.

--Jeff Moden