VBA to T-SQL via TDD: Step 7

Step 7. Execute the test query in SQL Server to verify it fails

VBA to T-SQL via TDD: Step 7

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.

Referenced articles

How to Convert a VBA Function to a SQL Server Scalar Function
MS Access Developers Can Remove All the Risk From This Complex Process With These 3 Words
Trust But Verify
What Ronald Reagan’s Words Can Teach Us About the Sample Code We Find on the Internet
VBA to T-SQL via TDD: Step 6
Step 6. Build a test query in SQL Server to return failing tests
VBA to T-SQL via TDD: Step 5
Step 5. Create a dummy scalar function in SQL Server

All original code samples by Mike Wolfe are licensed under CC BY 4.0