Creating and Running Test Cases in tSQLt

The following tutorial gives a brief introduction to creating test cases and a variety of methods to execute them. Before using any of the tSQLt procedures however, you must download them and install them in your database.

Creating Test Cases

Test cases in tSQLt are grouped into test classes. A test class is essentially a SQL schema with special properties applied so that tSQLt recognizes it as a test class.

To get started, you must create a test class. For example:

EXEC tSQLt.NewTestClass 'MyTestClass';
GO

Note: When you create a test class, if a schema with that same name already exists, the schema (and all the objects in it) is dropped. The schema is then re-created as a test class schema.

Creating a test case is easy. You only need to create a stored procedure on your test class schema. The procedure name must begin with “test”. If you quote the name in your CREATE PROCEDURE statement, you can even include spaces in the name of the test. For example:

CREATE PROCEDURE [MyTestClass].[test addNumbers computes 2 plus 2 equals 4]
AS
BEGIN
    DECLARE @actualComputedResult INT;
    SET @actualComputedResult = dbo.addNumbers(2, 2);
    EXEC tSQLt.AssertEquals 4, @actualComputedResult;
END;
GO

You can also have a SetUp procedure
which is called before executing each test case on the test class. To
create a SetUp procedure, simply create stored procedure named
“SetUp” in your test class schema. For example:

CREATE PROCEDURE [MyTestClass].[SetUp]
AS
BEGIN
    PRINT 'Do some setup work';
END;
GO

Running Test Cases

tSQLt provides a variety of ways to run test cases. These are summarized below:

1. RunAll
RunAll executes all test cases on all test classes. Test classes must have been created using the tSQLt.NewTestClass procedure in order for RunAll to find them.

2. Run
Run is a versatile procedure for executing test cases. It can be called in three ways:
a. With a test class name
b. With a qualified test case name (i.e. the schema name and the test case name)
c. With no parameter

When called with a test class name, Run executes all the test cases in that test class. If called with a qualified test case name, Run executes only that test case (and the test class’s SetUp if one exists).

The Run procedure “remembers” what parameter was used the last time it was called. When called with no parameter, Run executes the last executed test class or test case that was executed with the Run procedure.

Examples

-- Runs all the test classes created with tSQLt.NewTestClass
EXEC tSQLt.RunAll;

-- Runs all the tests on MyTestClass
EXEC tSQLt.Run 'MyTestClass';

-- Runs [MyTestClass].[test addNumbers computes 2 plus 2 equals 4] and executes the SetUp procedure
EXEC tSQLt.Run 'MyTestClass.[test addNumbers computes 2 plus 2 equals 4]';

-- Runs using the parameter provided last time tSQLt.Run was executed
EXEC tSQLt.Run;