VBA to T-SQL via TDD: Step 6

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.

How do you prove a negative?

When I did the wiring in my finished basement, I used a non-contact voltage meter to test for live electricity before I touched any bare wires.  These devices beep when they are near live wires.  If the wire is dead (i.e., safe to touch), then the device does nothing.

There is a very important three-step process to check wires with such a device:

1. Place the device next to a known live wire–it should beep
2. Place the device next to the wire you are checking–it should not beep
3. Place the device next to a known live wire–it should beep

Why bother with steps 1 and 3?  Because there are several reasons why the voltage tester might NOT beep when placed next to a live wire:

• You forgot to turn it on
• The batteries in the voltage tester are dead (or weak)
• You're not holding the voltage tester close enough to the wire
• The sheathing on the wire is too thick for the voltage tester to detect power

By proving a positive condition (existence of the live wire) both before and after proving a negative condition (absence of a live wire), we increase the reliability of our negative proof.

So What?

What does any of this have to do with test-driven development?

The only way to be sure that our tests are working is if we can show that they both pass and fail as we make changes outside of the test.  Let's consider a contrived example.  Say you have the following Assert statement in your VBA code:

Dim Coll As New Collection
' ... some other code ...
Debug.Assert Not (Coll Is Nothing)   'Make sure Coll is initialized

Many beginning developers would see the above code and expect the assertion (i.e., test) to fail unless the ' ... some other code ... includes a line like this: Set Coll = ....  But, in fact, the test will always pass because of the As New modifier in the declaration line.

If you don't verify your tests fail, you can never really be sure if they are passing.

Test Query Template

The basic template of our test query looks like this:

SELECT Input1, Input2, Input3, ExpectedValue
     , dbo.MyScalarFunction(Input1, Input2, Input3) As CalculatedValue
FROM dbo.MyTestData
WHERE ExpectedValue <> dbo.MyScalarFunction(Input1, Input2, Input3)

When we run this query, it will return one record for every failing test.

Banker's Rounding Example Continued

After populating our test data in Step 4 and creating a dummy function in Step 5, it's time to write some failing tests.  Let's adapt the Test Query Template above for the banker's rounding example:

SELECT NumberToRound, NumDigitsAfterDecimal, ExpectedValue
     , dbo.VBA_Round(NumberToRound, NumDigitsAfterDecimal) AS CalculatedValue
FROM dbo.RoundTestData
WHERE ExpectedValue <> dbo.VBA_Round(NumberToRound, NumDigitsAfterDecimal)

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

No Longer SetMike Wolfe

Trust But Verify

What Ronald Reagan’s Words Can Teach Us About the Sample Code We Find on the Internet

No Longer SetMike Wolfe

VBA to T-SQL via TDD: Step 4

Step 4. Use the results of your Access query to populate the test table

No Longer SetMike Wolfe

VBA to T-SQL via TDD: Step 5

Step 5. Create a dummy scalar function in SQL Server

No Longer SetMike Wolfe

UPDATE [2021-09-14]: Changed the test query template and example to include the function inputs as standalone columns for easier debugging of test failures.  Moved the final paragraph about executing the query to the next article (Step 7).