Trust But Verify

Yesterday, I wrote about the ten steps you can follow to convert a VBA function to a SQL Server scalar function.  

Those ten steps can be reduced to three words: test-driven development.  The steps are nothing more than applying TDD principles to the specific problem of converting a VBA function to SQL Server.  That said, they are difficult to explain–and even harder to comprehend–without a practical example.

Let's convert the VBA.Round function to a SQL Server scalar function.

Why VBA.Round?

VBA and SQL Server both have built-in Round functions, but they produce different results.

The VBA Round function uses banker's rounding.  The SQL Server Round function does not.  This means that Round(2.5, 0) will return 2 in VBA but 3 in SQL Server.  This is a problem if you want to migrate business logic that relies on rounded numbers from VBA to SQL Server.

To achieve success, we need to create 100% functional equivalence between the VBA Round function and our custom SQL Server scalar function.

Programming Advice from Ronald Reagan

"Trust, but verify."

There are several pages on the web with solutions for implementing bankers rounding in SQL Server.  Any one of them might be perfect.  But there's a good chance many of them are merely very good.    In my situation, very good was not going to cut it.  I needed my SQL Server banker's rounding function to produce output identical to output from the VBA.Round function.

It's one thing to trust third-party code from a reputable site like StackOverflow, but before putting it into production you should always verify it does what you expect.

And to do that, we will follow the ten steps from yesterday's article.

Referenced articles

External references

Image by WikiImages from Pixabay