This is Part 1 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.
Testing Against the Dummy Function
CREATE FUNCTION dbo.VBA_Round( @OriginalVal sql_variant, @RoundDigits int ) RETURNS float AS BEGIN RETURN 0 END;
The test query returned 17 rows, which means that 17 out of 19 test cases failed:
Testing the Refactored Function
In Step 8, we refactored the VBA_Round() function to address these 17 failures. Here's the ALTER FUNCTION statement we ran:
ALTER FUNCTION [dbo].[VBA_Round]( @OriginalVal sql_variant, @RoundDigits int ) RETURNS float AS BEGIN RETURN Round(Cast(@OriginalVal as float), @RoundDigits) END;
After executing the above statement, it is time to re-run the test query.
The test query now returns only four records (down from 17). In other words, the results below represent the only tests from our test suite that are still failing. Once we get these remaining tests to pass, we are home free.