VBA to T-SQL via TDD: Step 10

This is Part 10 of my series on converting a VBA function to a SQL Server scalar function using test driven development.  As a proof of concept, I will be re-implementing the VBA Round function (which uses bankers rounding) as a SQL Server scalar function.

Test-driven development is an iterative process.

You start with a dummy/placeholder function.  Write a bunch of tests.  Make sure they fail. Refactor the function.  Rerun the tests. Refactor. Rerun. Lather. Rinse. Repeat. Until all the tests pass.

Banker's Rounding Example

In Step 9 of our example, we had four failing tests:

These four failures get to the heart of the difference between traditional rounding and banker's rounding.  In traditional rounding, you "round half away from zero."  In banker's rounding, you "round half toward even."

Combining the Refactor & Rerun Tests Steps

In SQL Server Management Studio, we can save some time by combining steps 8 and 9 into a single query window.  The ALTER FUNCTION statement must be the only statement in a batch, so we use the SSMS GO keyword to process the statement in its own batch.

Here's what the concept looks like in practice (you may need to open the image in a new browser tab to see it full-size):

Here's the working function, inspired by the article, SQL Server Rounding Methods, from the LessThanDot blog:

ALTER FUNCTION [dbo].[VBA_Round](
    @OriginalVal float,
    @RoundDigits int
)
RETURNS float
AS
BEGIN
	RETURN CASE When Abs(@OriginalVal - Round(@OriginalVal, @RoundDigits, 1)) * Power(10, @RoundDigits+1) = 5 
                Then Round(@OriginalVal, @RoundDigits, Case When Convert(int, Round(abs(@OriginalVal) * power(10,@RoundDigits), 0, 1)) % 2 = 1 Then 0 Else 1 End)
                Else Round(@OriginalVal, @RoundDigits) 
                End
END;

Final notes

If you've been following along with this series, you may notice that I changed the type of the @OriginalVa input from sql_variant to float.  Keep in mind that the float type is an approximate numeric value, not an exact numeric value.  Depending on your needs, you may want to create a separate function (something like VBA_Round_Exact) that takes and returns a decimal type.

External references

LessThanDot - SQL Server Rounding Methods

There are various ways to round a number, and most of us don’t give it much thought, but we should. There are several methods for rounding: Round Up, Round Down, Round Away From Zero, Round Toward Zero, and Round Toward Even (also known as bankers rounding, unbiased rounding, Gaussian rounding, and …

LessThanDotGeorge Mastros (gmmastros)

Referenced articles

How to Convert a VBA Function to a SQL Server Scalar Function

MS Access Developers Can Remove All the Risk From This Complex Process With These 3 Words

No Longer SetMike Wolfe

Trust But Verify

What Ronald Reagan’s Words Can Teach Us About the Sample Code We Find on the Internet

No Longer SetMike Wolfe

VBA to T-SQL via TDD: Step 9

Step 9. Execute the test query in SQL Server

No Longer SetMike Wolfe

A Rounding We Will Go

Two kinds of rounding, the VBA language spec vs. the Office VBA implementation, and a drop-in replacement for VBA.Round().

No Longer SetMike Wolfe