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

Referenced articles