> ## Content Index
> Fetch the complete content index at: https://nolongerset.com/llms.txt
> Use this file to discover other available public pages before exploring further.

# VBA to T-SQL via TDD: Step 6
- URL: https://nolongerset.com/vba-to-tsql-via-tdd-step-6/
- Published: 2021-09-14T02:21:35.000Z
- Updated: 2026-05-08T13:02:25.000Z
- Description: Step 6. Build a test query in SQL Server to return failing tests
- Author: Mike Wolfe
- Tags: VBA to T-SQL via TDD, #Import 2026-05-20 02:59

*This is Part 1 of [my series](https://nolongerset.com/vba-to-tsql-via-tdd/) 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](https://nolongerset.com/trust-but-verify/) (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:

```vba
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:

```sql
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](https://nolongerset.com/vba-to-tsql-via-tdd-step-4/) and creating a dummy function in [Step 5,](https://nolongerset.com/vba-to-tsql-via-tdd-step-5/) it's **time to write some failing tests**. Let's adapt the Test Query Template above for the [banker's rounding example](https://nolongerset.com/trust-but-verify/):

```sql
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 FunctionMS Access Developers Can Remove All the Risk From This Complex Process With These 3 Words![](https://nolongerset.com/favicon.png)No Longer SetMike Wolfe![](https://storage.ghost.io/c/eb/d7/ebd732c1-5f03-4f07-b386-5d08557e15c9/content/images/2021/09/caterpillars-2869783_1920.jpg)](https://nolongerset.com/vba-to-tsql-via-tdd/)

[Trust But VerifyWhat Ronald Reagan’s Words Can Teach Us About the Sample Code We Find on the Internet![](https://nolongerset.com/favicon.png)No Longer SetMike Wolfe![](https://storage.ghost.io/c/eb/d7/ebd732c1-5f03-4f07-b386-5d08557e15c9/content/images/2021/09/president-67550_1920.jpg)](https://nolongerset.com/trust-but-verify/)

[VBA to T-SQL via TDD: Step 4Step 4\. Use the results of your Access query to populate the test table![](https://nolongerset.com/favicon.png)No Longer SetMike Wolfe![](https://storage.ghost.io/c/eb/d7/ebd732c1-5f03-4f07-b386-5d08557e15c9/content/images/2021/09/Step4-1.png)](https://nolongerset.com/vba-to-tsql-via-tdd-step-4/)

[VBA to T-SQL via TDD: Step 5Step 5\. Create a dummy scalar function in SQL Server![](https://nolongerset.com/favicon.png)No Longer SetMike Wolfe![](https://storage.ghost.io/c/eb/d7/ebd732c1-5f03-4f07-b386-5d08557e15c9/content/images/2021/09/Step5.png)](https://nolongerset.com/vba-to-tsql-via-tdd-step-5/)

***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](https://nolongerset.com/vba-to-tsql-via-tdd-step-7/)).*