Solved Scalar function to return result rounded to 2 decimal places (1 Viewer)

[Isaac]

I have a scalar function - and by the way I realize this may not even be proper for a function, as it requires minimal code to type out anyway, but part of the reason I am doing this is so that I am 'sure' the logic is working correctly - i.e., it's easier to create the function and then test it by quickly passing in different values compared to typing it out in the query and running the query with differing input data...

I want to take input numbers which can be things like 0.00, 1.15, 27.565423, etc
and execute some logic - very simple logic - and I want the result to be rounded to the nearest 2 decimal places.

When I create this function, and pass in select dbo.CA_CURT_HeightColumnOnOutputFile(5.5,10.5,15.5) , the result comes back as 6 rather than 5.50 or 5.5
Why? I know I'm missing something about how the data is being seen as it gets passed in and dealt with ... Thanks for looking

alter function [dbo].[CA_CURT_HeightColumnOnOutputFile]
(
    @BoxHeightInches    decimal,
    @ItemHeightInches    decimal,
    @RetailHeightInches    decimal
)
returns decimal

as begin
    declare @result decimal(19,2)
    
    --if box height inches is greater than 0, just return box height inches
    if @BoxHeightInches > 0
        begin
            set @result = round(@BoxHeightInches,2)
        end

    --elseif item height inches > 0 then return that
    else
        if @ItemHeightInches >0
            begin
                set @result=round(@itemheightinches,2)
            end
    
    --elseif retail height inches > 0 then return that
    else
        if @RetailHeightInches > 0
            begin
                set @result = round(@RetailHeightInches,2)
            end
    --else return 0
    else
        set @result=cast(0 as decimal(19,2))

    return @result
end
GO

 

[pbaldy]

Specifying the precision and scale works. The default scale is 0, which means no digits to the right of the decimal.

alter function [dbo].[CA_CURT_HeightColumnOnOutputFile]
(
    @BoxHeightInches    decimal(19,2),
    @ItemHeightInches    decimal(19,2),
    @RetailHeightInches    decimal(19,2)
)
returns decimal(19,2)
...

 

[Isaac]

Uh, gosh, I'm an idiot. Of course! The [default precision] of "18,0" that shows when I hover over my input parameters & return type should have clued me in, but I'm very tired today. Add to that my SQL Server experience has, for some reason, not included a lot of work with numbers generally.

Thanks, Paul - very much appreciated!

 

[pbaldy]

Happy to help Isaac! We've all had those brain cramps.