VBA to T-SQL via TDD: Step 3

Step 3. Create a temporary test table in SQL Server

Mike Wolfe

Sep 9, 2021 • 3 min read

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)

CREATE TABLE Template

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 (
 ID int NOT NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED
,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 (
 ID int NOT NULL IDENTITY (1,1) PRIMARY KEY CLUSTERED
,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).