VBA to T-SQL via TDD: Step 2

Step 2. Build a "test table population query" in Microsoft Access

VBA to T-SQL via TDD: Step 2

This is Part 2 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.

After identifying test data in Microsoft Access, the next step is to make it as easy as possible to move that data into SQL Server so that we can run our tests.  

There are a few different approaches you can take.  If you have a large amount of test data (thousands of records or more), you probably want to use the SQL Server bulk copy utility.  If you have fewer records, I prefer a simpler approach: writing a SELECT query in Access that returns an INSERT INTO...VALUES statement for each row of test data.

Query Results

I think the concept is easiest to explain if we work backwards from the finished result.  

Here is the output from the sample query (I'm using using the sample data I showed in Step 1):

Sample Query

Here is the SQL statement used to build the output shown above:

SELECT "INSERT INTO RoundTestData " & 
 "  (NumberToRound, NumDigitsAfterDecimal, ExpectedValue) " & 
 "VALUES (" 
 & [NumberToRound] & ", "
 & [NumDigitsAfterDecimal] & ","
 & Round(NumberToRound, NumDigitsAfterDecimal) & ")" AS TSQL
FROM RoundTestData;

Query Explanation

The query has a single field: the generated T-SQL that we will be executing against our SQL Server test database.

The first n - 1 fields are the inputs to whatever function in VBA we are trying to migrate to SQL Server.  In this case, those are the two inputs to the VBA Round function:

  1. Number
  2. NumDigitsAfterDecimal

The nth field is the actual output of the function (i.e., the expected value).  

The way we generate the expected value is to call the function as part of our Access query.  We use the values from the first n - 1 fields to pass as inputs to the function.  That function call can be seen in the second-to-last line in the sample SQL statement above: Round(NumberToRound, NumDigitsAfterDecimal).

Finally, note the double-quotes and ampersands in the Access SQL.  Those are required because we are building a single string field (the INSERT INTO statement).  We will copy and paste the results of this query into SQL Server Management Studio and execute them as part of Step 4.

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

External references

bcp Utility - SQL Server
The bulk copy program (bcp) utility bulk copies data between an instance of SQL Server and a data file in a user-specified format.
Round function (Visual Basic for Applications)

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