Trust But Verify

What Ronald Reagan's Words Can Teach Us About the Sample Code We Find on the Internet

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

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

External references

Round function (Visual Basic for Applications)
Trust, but verify - Wikipedia
bankers rounding in SQL Server - Google Search
SQL Server Rounding Issue where there is 5
As far as i know according to mathematics rounding should work as below when rounding number is 5. 2.435 => 2.44 (Round Up, if rounding to digit(3) is odd number)2.445 => 2.44 (Round Down, if

Image by WikiImages from Pixabay

All original code samples by Mike Wolfe are licensed under CC BY 4.0