When unit testing code there is a really powerful feature that is either called mocking or faking or sometimes using stubs and what these mean is creating objects that look like real objects but aren’t.

Why would you want something that looks like something but isn’t?

Unit testing means breaking your code into small chunks and testing that the small chunk does what it is meant to. Testing small chunks of code in isolation is difficult as they often call other chunks of code so we test and pretend that the other thing is correct and to validate those other small chunks, we unit test them.

What the hell does that mean?

If we look at an example, we have a schema in sql that looks like:

We want to write a unit test for a stored procedure that looks something like:

If we run the stored procedure, it will insert data into the audit log table and the current run processing log table. The audit log insert requires a foreign key which should be individually unit tested - all we care about for this test is whether or not the stored procedure returns the id for ‘Spice Girls’ when called.

If we just call the stored procedure, it will run through all the code and validate all of the check constraints and all of the foreign keys, the problem is that this is:

• Slow
• Means we have to make sure we have all the data in all reference tables setup
• If there is an error in one of those constraints or foreign keys, this test will start failing - instead we only want the tests that are actually covering those parts to break

This is why we create fake or mock objects. In tSQLt this is done using FakeTable and FakeFunction - you simply give it the name of the table and it effectively gives you a new table (it renames the existing one) with the following definition:

This means that there are no extra bits of code getting in our way and we can test what we actually need to. Instead of the setup to this test, inserting test data or checking for test data in all of the separate tables we can simply insert the values we will use in the columns we will use.

Once you have all the different items of code under their own unit test then if something breaks you can quickly see what it is and fix it.

The complete test for this procedure might end up looking something like: