This is the second in a series of articles on tSQLt:

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

Splitting into files and running tests

tSQLt organises test cases into test suites.  A test case is a stored procedure whose name starts with the word test.  A test suite is a schema that tSQLt knows about, and will normally contain one or more test cases.

Therefore, the top of a file of tSQLt tests will be something like this

USE myDatabase
GO

-- this creates the db schema and tells tSQLt that it contains tests
EXEC tSQLt.NewTestClass 'MyTestSuite';
GO

You can run the following amounts of test via different tSQLt commands

EXEC tSQLt.Run ‘myTestSchema.testA’	Run the single stored procedure (test case) called testA, in the schema (test suite) myTestSchema
EXEC tSQLt.Run ‘myTestSchema’	Run all stored procedures whose name starts with test in the schema myTestSchema
EXEC tSQLt.RunAll	Run all tests

The output will show tests’ results sorted by test suite name, then by test case name.  A test could pass, fail or give an error.  An error is raised when the test can’t complete, e.g. you have a bug in your test code.

Structuring one file

Each test case must have its own stored procedure whose name starts with test, but it can be helped by other stored procedures if necessary.  If the schema contains a stored procedure whose name is SetUp, this is run before each test case.  This is good for set-up that is shared by all test cases without any variation – you can’t pass parameters to SetUp to tailor its behaviour per test case.

I often use one stored procedure to set things up for a test, and another stored procedure to execute the code under test, gather up the actual results and compare them to expected results.  These are sometimes split up further into several stored procedures.  The main stored procedure for a test case then calls the prepare stored procedure and the run-and-check stored procedure, passing in parameters specific to the particular test case.

CREATE PROCEDURE MyTestSchema.Prepare
@thingOne INT,
@thingTwo VARCHAR(20)
AS
BEGIN
    -- proper stuff here, mocking tables and stored procedures, setting up data etc. …
END
GO

CREATE PROCEDURE MyTestSchema.RunAndCheck
@thingThree BIT,
@thinkFour INT
AS
BEGIN
    EXEC MyNormalSchema.ProcUnderTest @thingThree, @thingFour

    -- gather up the actual results …

    -- assert that they match expected results …
END
GO

CREATE PROCEDURE MyTestSchema.[test case A]
AS
BEGIN
    EXEC MyTestSchema.Prepare @thingOne=17, @thingTwo=’alpha’
    EXEC MyTestSchema.RunAndCheck @thingThree=0, @thingFour=88
END
GO

CREATE PROCEDURE MyTestSchema.[test case B]
AS
BEGIN
    EXEC MyTestSchema.Prepare @thingOne=942343, @thingTwo=’something big’
    EXEC MyTestSchema.RunAndCheck @thingThree=1, @thingFour=3
END
GO

The rest of this article will follow the standard structure of a unit test: Arrange, Act and Assert.  This is what you would expect to find inside each stored procedure whose name starts with test, although this might be delegated to several stored procedures, as above. Hints for how to actually create a test using the elements described below are in the article Practical considerations.

Arrange

This is where we get everything ready, which means:

1. Mocking any tables the stored procedure under test needs for input or output
2. Setting up data in those tables
3. Mocking any stored procedures or functions that the stored procedure under test uses
4. Creating tables as necessary for expected and actual results
5. Preparing expected results

The background for mocking tables, stored procedures and functions is in the article Introduction to tSQLt.

Mocking a table

To mock the table mySchema.A you just do this

EXEC tSQLt.FakeTable ‘mySchema.A’

If you want to test the constraint called myConstraint (like a foreign key), add it to the clone of table A using

EXEC tSQLt.ApplyConstraint ‘mySchema.A’, ‘myConstraint’

If you want to test the trigger called myTrigger

EXEC tSQLt.ApplyTrigger ‘mySchema.A’, ‘myTrigger’

Setting up data in mocked tables

The mocked version of table A looks just like the real version of table A (apart from any foreign keys that you have chosen to omit).  Therefore you can just insert into it as normal.

It’s worth adding in data that you expect to be missed by any WHERE clauses in the stored procedure under test.  It’s tempting to write only data needed to support the expected behaviour, but I have found several bugs by adding in near-miss type input data.  What a near miss means will depend on the WHERE clauses in question.  The important thing is to make sure that the near miss data will affect the result, e.g. the wrong rows deleted, the wrong total produced etc.

Mocking stored procedures and functions

The simpler version of mocking a stored procedure, where the stored procedure being called is replaced by just an insert that logs the fact that the stored procedure would have been called, is:

EXEC tSQLt.SpyProcedure ‘mySchema.myProcedureName’

If you want to add some SQL that is added along with the logging insert statement, so it’s executed when the code under test is run, create the NVARCHAR variable @mySQLstring and then:

EXEC tSQLt.SpyProcedure ‘mySchema.myProcedureName’, @mySQLstring

To mock a function, by replacing the function mySchema.myFunctionName with the function myOtherSchema.myReplacementFunctionName, declare the function myOtherSchema.myReplacementFunctionName and then do:

EXEC tSQLt.FakeFunction ‘mySchema.myFunctionName’, ‘myOtherSchema.myReplacementFunctionName’

Creating tables as necessary for expected and actual results

This is just normal SQL Server table creation.  It’s up to you whether you use normal tables (possibly in a schema that you use just for test stuff) or temporary tables.

Preparing expected results

This is more normal SQL stuff to put data in tables.  You might be able to re-use the tables that you faked and put initial data in, but that depends on your code and tables.  You might be able to make life easy for yourself here, by having a stored procedure that both sets up initial data in main tables and expected results.

Act

This is the most straightforward bit of the test – you just execute the stored procedure under test, passing in parameters as appropriate for the test.

Assert

After the code under test has run, you might need to do some work to get actual results into the right form and place, or they might be fine as they are.  If you need to do this, it’s just normal SQL Server selects and inserts.  Once you have the actual results where they need to be you can check them against the expected results.

To check if two ints, strings etc. are the same, assuming that they are in the variables @expected and @actual:

EXEC tSQLt.AssertEquals @expected, @actual, ‘my message for when the test fails’

This seems to work with lots of different data types.

To compare two tables, e.g. mySchema.ExpectedValues and myOtherSchema.ActualValues:

EXEC tSQLt.AssertEqualsTable ‘mySchema.ExpectedValues’, ‘myOtherSchema.ActualValues’, ‘my message for when the test fails’

Rows in the two tables are matched column-by-column, so they don’t need to be in the same order.  If there are any differences, you will see all rows in the output.  Rows that have matched will be shown once, with an ‘=’ in an extra first column.  Rows in only the expected results will have an ‘<’ in the extra first column, and rows in only the actual results will have an ‘>’ in the extra first column.

If you can’t easily compare two things, but instead do some other custom logic to see if the test has passed or failed, you can do the following as appropriate

EXEC tSQLt.AssertEquals 1, 1

EXEC tSQLt.Fail, ‘my message’

You can actually have up to 9 separate messages after tSQLt.Fail, which are concatenated in order.

An example of using Fail is below.

Example test 1 – a simple test

This is a simple example that shows how the various bits might come together.

CREATE PROCEDURE myTests.[test output is created for an odd input]
AS
BEGIN
    --- Arrange
    EXEC tSQLt.FakeTable ‘mySchema.myOutputTable’

    DECLARE @oddValue INT = 17;

    -- creates and populates expected results table
    EXEC myTests.CreateExpectedOutput @oddValue
 
    -- Act
    EXEC mySchema.myStoredProc @inputParam=@oddValue

    -- Assert
    EXEC tSQLt.AssertEqualsTable ‘myTestSchema.ExpectedResults’, ‘mySchema.myOutputTable’, ‘output is created for an odd input’
END
GO

Example test 2 – testing for exceptions

This is a silly procedure that you want to throw an exception for odd numbers but not even numbers, and a test (you would also want a test with an even number).

CREATE PROCEDURE myMainSchema.doNotLikeOddNumbers
@numToTest INT
AS
BEGIN
    IF @numToTest % 2 = 1
        THROW 200000, ‘I do not like odd numbers’, 1
END
GO

CREATE PROCEDURE myTestSchema.testOddNumber
AS
BEGIN
    BEGIN TRY
        EXEC myMainSchema.doNotLikeOddNumbers, @numToTest=3
        EXEC tSQLt.Fail ‘there was no exception when there should have been’
    END TRY

    BEGIN CATCH
        EXEC tSQLt.AssertEquals 1, 1
    END CATCH
END
GO