Making Fakes in SSDT Database Unit Tests

When we test SQL Server stored procedures or functions there may be situations where one object (for example, stored procedure) calls another object (for example, a scalar function). In this case we have dependency between objects.

One of the method to test the functionality of dependent objects is to use fake objects, i.e. we use a stub function instead of a real one. In this post I’ll show how to substitute a function with a fake.

I’ve created a very simple function that multiply an integer value by 2.

I’ve created a simple unit test for that function in SQL Server Data Tools with the only test condition to check that 1 * 2 = 2.
fake unit test
My test passes. Now I want to replace my function with a fake that will always return a value 2.
This can be made in pre-test section by renaming the real function and creating a stub.
fake pre-test
There’s two important things in this script:
  1. It does not check the existence of a new function name and it does not delete it. If this new name already exists, the unit test throw an error.
  2. You can’t create a stub function with a direct CREATE FUNCTION command due to an error in SQL batch. Therefore, I use a wrapper to execute SQL command dynamically.
In post-test section I safe-delete my stub function and revert the name back.
fake post-test
The test passes. That’s all right!
Using a fake object allows us to test each database object separately but it needs some efforts from a developer.

Leave a Reply

Your email address will not be published. Required fields are marked *