Testing the code (T-SQL Tuesday #156)

Foreword 🔗︎

Even though I picked the question, I struggled to answer it. Following my train of thought - production code should be of the highest quality. To enforce quality, we use quality gates. And the one I value above all is testing.

We want to ensure the code behaves as expected when we deploy it to production. And because the only constant is “change”, ideally, we want the tests to be automated so the next person that touches the code doesn’t have to repeat all that work.

I’ll cover unit tests and their benefits on a regex example because unit testing SQL code is too large of a scope for one article.

Example 🔗︎

My use case was to find static references to procedure names in a C# codebase. To start, I only had a few simple criteria

Procs can either have a schema or not All combinations of quoted schema / proc name The schema and proc name are captured without the brackets. I’m a fan of Test-driven development because it matches reality. Therefore, I have specifications before I write any code.

Let’s start creating the unit tests. I’m using the regex101 and a .NET flavour.
I need to provide a description and an assertion to create unit tests.

Ideally, I should add more tests, especially for false positives and values I don’t want to capture. This might be a bit of trial and error.

Next, I’ll create a “simple” regex that will satisfy the criteria.

^(?:\[?(\w+)]?\.)?\[?(\w+)]?$

You can check the regex, unit tests and the explanation here.

This will serve as living documentation containing all the use cases. There is also an explanation of that regex on the right-hand side.

Next time, I can improve the performance or add a new requirement: Stored procedures can be versioned using format #V123 (hash symbol and letter V followed by numbers), and we want to capture this version. Because that way, we can check if our code base may call two different versions of the procedure.

The new developer can add new unit tests, and if they cause a regression, it’s detected instantly during the early phases of development, thus providing a fast feedback loop.

Thank you for reading.