How to Convert a VBA Function to a SQL Server Scalar Function

Mike Wolfe -

Many people assume that simply upsizing a backend database from Microsoft Access to SQL Server will solve all of their performance problems.

The reality is more complicated.  Upsizing alone usually leads to worse performance, at least initially.  For example, queries that rely on VBA functions to calculate values often run much slower when the data is stored in SQL Server.

The best way to turbocharge those queries is to migrate the VBA functions to equivalent SQL Server scalar functions.

SQL Server Scalar Functions

A SQL Server scalar function is a user-defined function (UDF) that returns a single value.  

SQL Server UDFs can also return tables; these are commonly referred to as table-valued functions (TVF).  A scalar function, then, is roughly equivalent to a typical VBA function.  It accepts one or more parameters and returns a single result.  

I can't tell you in one article how to convert any arbitrary VBA code into its equivalent T-SQL.  Instead, I can do the next best thing.

I can provide you with a common set of steps to ensure you can safely convert any VBA function to a functionally-equivalent SQL Server scalar function.

The 3 Words that Make Risk Go Away

Rewriting critical business logic is a high-risk situation.

Lots of things could go wrong.  But the biggest risk is the possibility of introducing logic errors.  And uncaught logic errors can be very expensive.  

All that risk can be eliminated with these 3 words: Test. Driven. Development.

Test Driven Development

Test driven development minimizes risk in complex software endeavors.

Here's the high-level overview:

  1. Write tests and confirm they fail
  2. Write code until all the tests pass

The biggest challenge with TDD is providing adequate test coverage.

It takes time to write each test.  You need enough tests to cover all the edge cases (the known knowns).  You need creativity to imagine the corner cases (the known unknowns).  And, of course, there are always those cases we don't anticipate, those scenarios that lie beyond our capacity for human aforethought (the unknown unknowns).  (Unfortunately for us, those obscure scenarios are not beyond Ned-in-accounting's capacity for creative software use.)

We can use Microsoft Access to overcome the test coverage challenge without even breaking a sweat.

VBA to T-SQL via TDD

There are 10 steps to convert a VBA function into a fully-tested SQL Server scalar function.

The first five steps are all about overcoming the test coverage challenge.  The next five steps are the basic steps in any test driven development scenario.  We will explore these steps in more depth in future articles.

  1. Identify or create test data in Microsoft Access
  2. Build a "test table population query" in Microsoft Access
  3. Create a temporary test table in SQL Server
  4. Use the results of your Access query to populate the test table
  5. Create a dummy scalar function in SQL Server
  6. Build a test query in SQL Server to return failing tests
  7. Execute the test query in SQL Server to verify it fails
  8. Alter the scalar function to address failures
  9. Execute the test query in SQL Server
  10. Repeat steps 8 & 9 until all tests pass

Referenced articles

Some Bugs are Better than Others
Not all bugs are created equal. Avoid the expensive ones by making more of the ones that are easy to find and fix.
No Longer SetMike Wolfe
Logic Errors
The logic error is the most dangerous and insidious of all software errors.
No Longer SetMike Wolfe
The Curse of Reliable Software
How does one avoid the reliability paradox? One option is to intentionally write unreliable, buggy software. There’s a better option.
No Longer SetMike Wolfe

External references

CREATE FUNCTION (Transact-SQL) - SQL Server
CREATE FUNCTION (Transact-SQL)
Microsoft DocsWilliamDAssafMSFT

Image by anncapictures from Pixabay

UPDATE [2021-09-10]: Switched steps 5 and 6 to avoid errors when creating a test query that would be calling a non-existent SQL Server scalar function.