VBA to T-SQL via TDD: Step 3

Step 3. Create a temporary test table in SQL Server

VBA to T-SQL via TDD: Step 3

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

There is one important thing you have to get right in Step 3.

The SQL Server data types must match the VBA data types.

Creating the test table in SQL Server is straightforward.  The test table should include the following fields:

  • Field 1: an autonumber ID to serve as a surrogate primary key
  • Fields 2 to (n - 1): the inputs to the VBA function/SQL Server scalar function
  • Field n: the output from the VBA function (i.e., the expected value)


As you are creating the table in SQL Server, be mindful of the data type mapping between VBA and SQL Server.  I've included a sample CREATE TABLE script below, along with the most commonly used VBA data types and their SQL Server counterparts:

CREATE TABLE dbo.MyTestData (
,Input1 float       /* Double   */ not null
,Input2 real        /* Single   */ not null
,Input3 money       /* Currency */ not null
,Input4 datetime    /* Date     */ not null
,Input5 int         /* Long     */ not null
,Input6 smallint    /* Integer  */ not null
,Input7 tinyint     /* Byte     */ not null
,Input8 sql_variant /* Variant  */ not null
,ExpectedValue nvarchar(max)  /* String */ not null

Sample CREATE TABLE Script

Let's use the above template to build a CREATE TABLE script for our banker's rounding example.  

CREATE TABLE dbo.RoundTestData (
,NumberToRound sql_variant not null
,NumDigitsAfterDecimal int not null 
,ExpectedValue float not null

Note how these fields map to the function signature for the VBA.Round function:

The VBA Round function accepts a Variant as the first parameter.  I'm using the sql_variant data type so that the SQL Server scalar function can be called in the same ways as the VBA Round function.

The return value from the VBA Round function is also a Variant.  The actual subtype of the return value varies based on the type of the first input parameter, Number.

However, there are some things to be aware of with the sql_variant data type.  Most notably, this:

ODBC does not fully support sql_variant. Therefore, queries of sql_variant columns are returned as binary data when you use Microsoft OLE DB Provider for ODBC (MSDASQL). For example, a sql_variant column that contains the character string data 'PS2091' is returned as 0x505332303931.

For that reason, I'm choosing to set the data type of the ExpectedValue field to float (i.e., the equivalent of the Double data type in VBA).

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

Comparing Access and SQL Server data types
sql_variant (Transact-SQL) - SQL Server
sql_variant (Transact-SQL)