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.
Red light = FAIL. Green light = PASS.
If you have used test-driven development (TDD) outside of SQL Server, you may be familiar with testing frameworks that show visual red or green icons for failing or passing tests, respectively. For example, here's a screenshot from Visual Studio's Test Explorer:
Keep It Simple, Stupid
Well, you can forget about that here.
There is no fancy testing framework you need to learn in this case. Instead of red = FAIL and green = PASS, we just have query results. Every row returned from our test query is a FAILING test. Every row that exists in the test data table, but that does not get returned from our test query is a PASSING test.
Here are the results from running the example test query from Step 6.
Look! We have 17 failing tests.
Keep in mind, at this point, our VBA_Round scalar function is hard-coded to return zero regardless of its inputs. As we implement the VBA_Round() function and the record count goes to zero, we can be confident it's because the function is doing what we want.
Remember, if you don't verify your tests fail, you can never really be sure if they are passing.