> ## 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 3
- URL: https://nolongerset.com/vba-to-tsql-via-tdd-step-3/
- Published: 2021-09-10T01:41:39.000Z
- Updated: 2026-05-08T13:02:28.000Z
- Description: Step 3. Create a temporary test table in SQL Server
- Author: Mike Wolfe
- Tags: VBA to T-SQL via TDD, #Import 2026-05-20 02:59

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

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](https://support.microsoft.com/en-us/office/comparing-access-and-sql-server-data-types-9188f41d-6c0e-4733-9d20-d08916f50bd2). I've included a sample CREATE TABLE script below, along with the most commonly used VBA data types and their SQL Server counterparts:

```sql
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](https://nolongerset.com/trust-but-verify/). 

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

![](https://storage.ghost.io/c/eb/d7/ebd732c1-5f03-4f07-b386-5d08557e15c9/content/images/2021/09/image-33.png)

The VBA Round function accepts a Variant as the first parameter. I'm using the [sql\_variant data type](https://docs.microsoft.com/en-us/sql/t-sql/data-types/sql-variant-transact-sql?view=sql-server-ver15) 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*.

![](https://storage.ghost.io/c/eb/d7/ebd732c1-5f03-4f07-b386-5d08557e15c9/content/images/2021/09/image-40.png)

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 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/)

### External references

[Comparing Access and SQL Server data types![](https://support.microsoft.com/apple-touch-icon.png)![](https://support.microsoft.com/images/Facebook-GrayScale.png)](https://support.microsoft.com/en-us/office/comparing-access-and-sql-server-data-types-9188f41d-6c0e-4733-9d20-d08916f50bd2)

[sql\_variant (Transact-SQL) - SQL Serversql\_variant (Transact-SQL)Microsoft DocsMikeRayMSFT![](https://docs.microsoft.com/en-us/media/logos/logo-ms-social.png)](https://docs.microsoft.com/en-us/sql/t-sql/data-types/sql-variant-transact-sql?view=sql-server-ver15)