Luis Cazares - Tuesday, May 16, 2017 6:27 AM

Your function has a problem when it's used against negative numbers. I wrote an article some time ago about this options which simulate CEILING and FLOOR. You can read it in here: http://www.sqlservercentral.com/articles/T-SQL/145448/
s
Here's a corrected version for your function

CREATE FUNCTION dbo.udf_RoundNearest
(
@Number bigint, /*Natural Number*/
@RoundNearest float, /*Power of 10, 10^n , 10,100,1000...*/
@Direction int /* 0-> Down , 1 -> Up */
)
RETURNS TABLE AS
RETURN
SELECT CASE WHEN @Direction = 0
    THEN ROUND(@Number-(.49*@RoundNearest),-LOG10(@RoundNearest))
    ELSE ROUND(@Number+(.49*@RoundNearest),-LOG10(@RoundNearest))
    END Number
GO