VBA to T-SQL via TDD: Step 1
Step 1. Identify or create test data in Microsoft Access
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 biggest challenge with test driven development is providing adequate test coverage.
A common approach to generating test cases in TDD is to try to imagine as many different scenarios as possible and create tests that cover those possibilities. The obvious limitation with this approach is that we are constrained by our own imagination as developers. And users will never stop coming up with new and inventive ways to expose the limits of our imagination.
When developing test cases, it's better to rely on the past than try to predict the future.
Past is Prologue
What does it mean to rely on the past for test cases?
Generally speaking, if you are converting a VBA function to a SQL Server scalar function, you are doing it for performance reasons. That usually means that you have a table full of data sitting around just waiting to be turned into test cases. What's more, there are likely several edge cases and at least a few corner cases within that table full of data.
If at all possible, then, your best bet is to use a table of existing data.
Generating Test Data
What if there is no suitable table of data to use as the basis of your test cases?
In that scenario, you will need to generate test cases from scratch. You will want to include the following kinds of cases:
- A typical case:
Round(1.3, 0) = 1 - A border case:
Round(2.5, 0) = 2 - A border case (in the other direction):
Round(3.5, 0) = 4 - An upper limit case (e.g.,
CByte(255)vs.CByte(256)) - A lower limit case (e.g.,
CByte(0)vs.CByte(-1)) - An implicit type conversion case:
Round("2.5", 0) = 2 - Edge/corner cases (by definition these are difficult to anticipate):
Round(CDbl(4/7), 8) = 0.57142857vs.Round(CSng(4/7), 8) = 0.5714286
Once your new function is performing in the wild, any reported bugs (i.e., unanticipated edge/corner cases) can be added as new test data.
Sample Data for the Round Function
Here is the sample data we will be using to test our banker's rounding SQL Server scalar function. This data is for demonstration purposes and is not meant to be an exhaustive list of test cases. That said, if you have an interesting edge or corner case that you think I should include, please leave a comment below.
Note that the only data I'm including in this step are the function inputs (for the VBA.Round function, that means Number and NumDigitsAfterDecimal). We will handle the function output (the rounded amount) in Step 2.
Referenced articles
Mike Wolfe
Mike Wolfe
