SQL Server database Banker's Rounding user defined function:

SQL Server database Bankers Rounding user define function like Math.Round in .NET, or round in VB6, VBA, Access, MySQL 4


CREATE FUNCTION BRound(@d DECIMAL(22,9), @prec INT) RETURNS DECIMAL(22,9)

AS
BEGIN


/*
Banker's Rounding v1.00, 2006-01-13
Copyright 2006 Michael Buen
miblogic@yahoo.com
http://miblogic.blogspot.com/

Provided under the GNU General Public License
Contact me for use outside the bounds of that license

---------------------------------------------------------------
This program is free software; you can redistribute it and/or
modify it under the terms of the GNU General Public License
as published by the Free Software Foundation; either version 2
of the License, or (at your option) any later version.

This program is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
GNU General Public License for more details.

The GNU General Public License can be found at:
http://www.gnu.org/copyleft/gpl.html
---------------------------------------------------------------

*/


-- michael buen bankers rounding algorithm


DECLARE @raisedNumber DECIMAL(31,9)
SET @raisedNumber = @d * POWER(10,@prec)

DECLARE @remainder DECIMAL(31,9)
SET @remainder = @raisedNumber - FLOOR(@raisednumber)


DECLARE @oddeven INT

SET @oddeven = convert(bigINT,@raisedNumber) % 2

-- IF odd or remainder > 0.5
IF @oddeven = 1 or @remainder > 0.5 BEGIN
RETURN round(@d, @prec)
END
ELSE BEGIN
RETURN (@raisedNumber - @remainder) / POWER(10,@prec)
END

RETURN NULL

END