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.
CREATE FUNCTION [dbo].[StubFunction]
RETURN @param * 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.
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.
There’s two important things in this script:
- 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.
- 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.
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.