This is the third article in a series on tSQLt:

1. Introduction
2. Anatomy of a test
3. Practical considerations
4. Dealing with transactions

Getting organised

One stored procedure under test is likely to need several stored procedures to test it properly.  This means that the number of stored procedures in your database will increase greatly.  (This is one reason why it’s probably not a good reason to run tSQLt on a production database.)  It’s worth putting some thought into how you are going to structure the tests both in terms of how they sit on the file system (in folders etc.) and how they sit in the database (in schemas etc.)

The details of this will depend on how the production code is structured.  Should tests sit in a directory alongside the code they test, or should they all be collected together into a separate test directory (with sub-directories etc.)?  This issue also relates to your build and deployment process – how can you deploy just the production code to production, but production and test code to a test server?

Writing a test

I have found myself often using the same steps when writing tests for a stored procedure or function.  It breaks down into two main parts:

1. What test cases do I need?
2. What implementation details of the stored procedure under test will influence how I write my tests?

I am going to skip the first one as this is general testing stuff that either you already know or I hope you can find from much cleverer people than me via the internet.  I usually just write down a one-line comment per test case before I implement any of them, to make sure I’ve covered everything.

I’ll go into the second one in a bit of detail.  First, I look at the inputs to the stored procedure under test, which are:

1. Input parameters
2. Tables it queries
3. Stored procedures and / or functions that it calls to get data

Then I look at the outputs, which are:

1. Output parameters
2. Return values from functions
3. Result sets from stored procedures (selects with no variable to catch their results)
4. Tables that are changed (insert, update or delete)
5. Stored procedures that it calls to act on data on its behalf e.g. insert a row into a table

Any stored procedures, functions or tables in either list will need to be mocked. If you are mocking a function you will need to write a replacement version of it (or several replacement versions, if this makes more sense).  If you are mocking a stored procedure, will the stored procedure under test still function?  If it won’t, you will need to supply a string of SQL to execute when you mock the stored procedure.

As I said in Anatomy of a test, it’s well worth creating test data that you expect your code to ignore, particularly data that will affect the code’s results.  This has found bugs in WHERE clauses that I thought were correct.

Data-driven testing

If you have written tests for things like C# before, you might have created tests that differ only in specific details on inputs and outputs.  To make this set of tests more concise, you might have used a feature of the testing framework where a template test is run many times, with holes in the template being filled from the next row of values passed in from some source of data like an array.

tSQLt doesn’t do this for you, because it’s easy enough to do it yourself.  You already have:

• a handy home for data to loop through (a table or table variable);
• a way of doing the looping (either a single insert / update statement if you’re testing a function, or a WHILE loop if it’s a stored procedure).