Situatie
Am facut o functie ce primeste 3 parametri, intre care primul este un numar intreg, pe care il rotunjeste la cea mai apropiata putere a lui 10 (in functie de parametrul al doilea), iar rotunjirea se face in sus sau in jos, in functie de parametrul al treilea.
Am lasat si cateva exemple de apelare comentate, pentru o mai buna intelegere a felului in care functioneaza functia.
Solutie
–SELECT * FROM dbo.RoundNearasrTen_fct (157615,1000,0)
–SELECT * FROM dbo.RoundNearasrTen_fct (157615,1000,1)
–SELECT * FROM dbo.RoundNearasrTen_fct (157615,100,0)
–SELECT * FROM dbo.RoundNearasrTen_fct (157615,100,1)
CREATE FUNCTION dbo.RoundNearasrTen_fct
(
@Number bigint,
@RoundNearest bigint, — 10,100, 1000 putere a lui 10
@Direction int — 0 = rotunjire in jos, 1 = rotunjire in sus
)
RETURNS TABLE AS
RETURN
SELECT ISNULL(CASE WHEN @RoundNearest>=@Number THEN @Number
ELSE
(
(@Number + CASE
WHEN @Direction = 0
THEN 0
ELSE CASE WHEN @Number % @RoundNearest = 0 THEN 0 ELSE @RoundNearest END
END) / NULLIF(@RoundNearest,0)) * @RoundNearest
END , @Number) Number
Leave A Comment?