tSQLt adds Function Mocking to the unit testing armoury

The latest release of tSQLt (version V1.0.5137.39257) was published last week, and you can download this version in the usual place. If you're using SQL Test with tSQLt, you can upgrade SQL Test to the latest tSQLt release by following these instructions.

(Note, if you've not come across tSQLt before, it's a database unit testing framework written in T-SQL, and there's a great article explaining why you might want to use it here. Red Gate have written SQL Test to help integrate the power of tSQLt with the SQL Server Management Studio environment. This article assumes you're familiar with tSQLt, but if not I'd strongly encourage you to look at it for unit testing your databases.)

I am pleased to see that this release adds function mocking, as well as a simpler way to rename classes than the previous work around, and checking for a specific error number to tSQLt.ExpectException.

Let's look at that function mocking in a bit more detail. Consider a function which should add two numbers together. We would want to unit test it, and we can do that in the normal way, but a test on a stored procedure which needs to isolate from that function needs to call our new tSQLt method tSQLt.FakeFunction.


CREATE FUNCTION dbo.AddTogether (@a INT, @b INT)
RETURNS INT
AS
BEGIN
   RETURN @a+@a
END;
GO

CREATE PROC Maths AS
  SELECT dbo.AddTogether(1,2) AS SumOfNumbers
GO

Now, we can see there is a bug in the function above (and if we run the Stored procedure, we will get a value of 2 returned), but let's ignore that function and proceed to test our stored procedure. Remember, because we are isolating from our dependencies, we don't expect out stored procedure to fail its unit tests.

The way that FakeFunction works is that you need to supply it with a stub function to use in place of your function to be isolated. I suggest you put them in the test class. So let's create a simple function that returns a static value - 3.

EXEC tSQLt.NewTestClass @ClassName = N'MathsTests' -- nvarchar(max)
GO

CREATE FUNCTION MathsTests.Fake (@a INT, @b INT) 
RETURNS INT
AS 
BEGIN
   RETURN 3
END
GO

Now we are ready to create our test:

CREATE PROC MathsTests.[test I get a value of 3 returned when I add 1 and 2] 
AS
--Assemble
EXEC tSQLt.FakeFunction @FunctionName = N'dbo.Addtogether', -- nvarchar(max)
            @FakeFunctionName = N'MathsTests.Fake' -- nvarchar(max)

CREATE TABLE MathsTests.Expected (SumOfNumbers INT)
CREATE TABLE MathsTests.Actual (SumOfNumbers INT)
INSERT MathsTests.Expected (SumOfNumbers)
VALUES (3)

--Act
INSERT MathsTests.Actual
EXEC tSQLt.ResultSetFilter 1,'exec dbo.Maths'
--Assert
EXEC tSQLt.AssertEqualsTable @Actual = 'MathsTests.Actual', 
                                               @Expected = 'MathsTests.Expected'
GO

If we run this test, we get a successful test, because the stored procedure under test returns a row with a value of 3, the expectation, as our code under test (the stored procedure) is isolated from the function which has the bug in it. Of course, you would want to ensure that any module from which you isolate is properly tested, so that you catch issues such as this, but this somewhat contrived example allows you to see how the true cause of the failure can then be found more easily.

The ability to fake functions in this way is a great addition to the unit test writer's armoury.