Jerry Nixon on Windows: MROUND() for TSQL

Jerry Nixon on Windows

Thursday, August 30, 2007

MROUND() for TSQL

CREATE FUNCTION [dbo].[fn_MROUND] ( @num int, @mult int )
RETURNS int
AS BEGIN

if ( @num % @mult = 0 )
RETURN @num

declare @resultUP int ;
set @resultUP = @num + ( @mult - ( @num % @mult ) ) ;

declare @resultDOWN int ;
set @resultDOWN = @num - ( @num % @mult ) ;

IF ( ABS(@num - @resultDOWN) > ABS(@num - @resultUP) )
RETURN @resultUP

RETURN @resultDOWN

END