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.
The key to test-driven development is to start with enough tests to cover every anticipated scenario.
If you do that, then the rest is easy.
- Review the failed tests
- Write code to address the failures
- Re-run the tests
- If all tests pass, you're done; otherwise, return to step 1
Banker's Rounding Example
In Step 7, we ran our test query to verify that all the tests fail when running against a dummy function that is hard-coded to always return zero. The test query returned 17 rows. In other words, 17 of our 19 test cases failed....which means that two test cases passed even with the dummy function!
Addressing the failing tests
Let's start by updating the scalar function to use SQL Server's built-in rounding function. If I didn't already know better, it would be reasonable to believe that using the Round function in T-SQL would generate identical results to the Round function in VBA.