CODEWITHSUNDEEP
sqlsql-servert-sqlfloating-point
roa765
roa765

Reputation: 295

SQL function not displaying two decimal places although input parameter value is float

I have a function that rounds to the nearest value in SQL as per below. When I pass my value in and run the function manually, it works as expected. However when I use it within a select statement, it removes the decimal places.

E.g. I expect the output to be 9.00 but instead I only see 9. 

CREATE FUNCTION [dbo].[fn_PriceLadderCheck]
    (@CheckPrice FLOAT,
     @Jur VARCHAR(10))
RETURNS FLOAT
AS
BEGIN
    DECLARE @ReturnPrice FLOAT

    IF (@Jur = 'SE')
    BEGIN
        SET @ReturnPrice = (SELECT [Swedish Krona ]
                            FROM tbl_priceladder_swedishkrona
                            WHERE [Swedish Krona ] = @CheckPrice +
                                                     (SELECT MIN(ABS([Swedish Krona ] - @CheckPrice))
                                                      FROM tbl_priceladder_swedishkrona)
                               OR [Swedish Krona ] = @CheckPrice -
                                                     (SELECT MIN(ABS([Swedish Krona ] - @CheckPrice))
                                                      FROM tbl_priceladder_swedishkrona))
    END

    IF (@Jur = 'DK')
    BEGIN
        SET @ReturnPrice = (SELECT [Danish Krone ]
                            FROM tbl_priceladder_danishkrone
                            WHERE [Danish Krone ] = @CheckPrice +
                                                    (SELECT MIN(ABS([Danish Krone ] - @CheckPrice))
                                                     FROM tbl_priceladder_danishkrone)
                               OR [Danish Krone ] = @CheckPrice -
                                                    (SELECT MIN(ABS([Danish Krone ] - @CheckPrice))
                                                     FROM tbl_priceladder_danishkrone))
        END

    RETURN @ReturnPrice 
END

Run SQL manually:

 declare @checkprice float
set @checkprice = '10.3615384615385'

SELECT [Swedish Krona ]
FROM tbl_priceladder_swedishkrona
WHERE [Swedish Krona ] = @CheckPrice +
      ( SELECT MIN(ABS([Swedish Krona ] - @CheckPrice))
        FROM tbl_priceladder_swedishkrona
      )
   OR [Swedish Krona ] = @CheckPrice -
      ( SELECT MIN(ABS([Swedish Krona ] - @CheckPrice))
        FROM tbl_priceladder_swedishkrona
      )

When I use this function with a SQL select statement for some reason it removes the 2 decimal points.

SELECT 
    Article, Colour, 
    dbo.fn_PriceLadderCheck([New Price], 'se') AS [New Price]
FROM 
    #temp2 t

[New Price] on its own is example output is 10.3615384615385

Any ideas?

Upvotes: 1

Views: 293

Answers (1)

AreEl
AreEl

Reputation: 61

Cast the result into a Decimal and specify the scale. See the example below.

RETURN SELECT CAST(@ReturnPrice AS DECIMAL(16,2))

Upvotes: 2

Related Questions