tSQLt test cases can be executed and included in the results of your continuous integration build. This section describes how to integrate tSQLt test results with the popular continuous integration tool, CruiseControl.

CruiseControl executes your build script on a schedule or when code is checked-in to the version control repository. It reports build errors automatically and can include test results. For this discussion, we’ll setup a build in Ant, although other builders can be used.

We want our build to perform the following steps:
1. Create the database which we are testing. It should include the creation of the tables, procedures and other objects.
2. Install tSQLt to the database.
3. Load our tSQLt unit tests into the database.
4. Execute the tests.
5. Get the results from the test cases and store them in a file.

Setting up the build
First lets tell CruiseControl about the build script we will create. After installing CruiseControl, you will find a config.xml in the installation folder. This config file includes your projects (CruiseControl includes a sample project called Connect Four). We’re going to create our own project:

<project name="MyDatabaseProject">
    <listeners>
        <currentbuildstatuslistener file="logs/${project.name}/status.txt"/>
    </listeners>

    <modificationset quietperiod="30">
        <filesystem folder="C:/projects/${project.name}"/>
    </modificationset>

    <schedule interval="300">
        <ant anthome="apache-ant-1.7.0" buildfile="c:/projects/${project.name}/build/build.xml">
            <property name="db.server" value="localhost"/>
            <property name="db.name" value="MyUnitTestDatabase"/>
        </ant>
    </schedule>
</project>

This simple build file executes a build every 5 minutes by calling the Ant script stored at c:/projects/MyDatabaseProject/build/build.xml. We are passing two properties to the Ant script: the server and database name which we will create and use to execute the tests.

The Build Script
Now, we’ll create the build script in c:/projects/MyDatabaseProject/build/build.xml. Here is a sample which completes all of the desired steps:

<project name="tSQLt.sourceforge" default="all">
    <target name="all" depends="db.recreate, db.tsqlt.install, db.tests.install, db.tests.run, db.tests.output, db.tests.check.results"/>

    <target name="db.recreate">
        <exec executable="cmd" failonerror="true">
            <arg value="/c"/>
            <arg value="sqlcmd -S ${db.server} -E -I -Q &quot;IF EXISTS(SELECT 1 FROM sys.databases WHERE name='${db.name}') DROP DATABASE ${db.name}; CREATE DATABASE ${db.name};&quot;"/>
            <arg value="sqlcmd -S ${db.server} -E -I -i &quot;CreateSchema.sql&quot;"/>
        </exec>
    </target>

    <target name="db.tsqlt.install">
        <exec executable="cmd" failonerror="true">
            <arg value="/c"/>
            <arg value="sqlcmd -S ${db.server} -d ${db.name} -I -i &quot;test/tSQLt.class.sql&quot;"/>
        </exec>
    </target>

    <target name="db.tests.install">
        <exec executable="cmd" failonerror="true">
            <arg value="/c"/>
            <arg value="sqlcmd -S ${db.server} -d ${db.name} -I -i &quot;test/TestCases.sql&quot;"/>
        </exec>
    </target>

    <target name="db.tests.run">
        <exec executable="cmd" failonerror="true">
            <arg value="/c"/>
            <arg value="sqlcmd -S ${db.server} -d ${db.name} -I -Q &quot;EXEC tSQLt.RunAll;&quot;"/>
        </exec>
    </target>

    <target name="db.tests.output">
        <exec executable="cmd" failonerror="true">
            <arg value="/c"/>
            <arg value="sqlcmd -S ${db.server} -d ${db.name} -h-1 -y0 -I -i &quot;test/GetTestResults.sql&quot; -o &quot;test/results/TestResults.xml&quot;"/>
        </exec>
    </target>

    <target name="db.tests.check.results">
        <exec executable="cmd" failonerror="true">
            <arg value="/c"/>
            <arg value="sqlcmd  -S ${db.server} -d ${db.name} -h-1 -y0 -I -Q &quot;:EXIT(SELECT COUNT(*) FROM tSQLt.TestResult WHERE Result != 'Success')&quot;"/>
        </exec>
    </target>
</project>

Details on each section of the build script
db.recreate: In this section we are dropping and recreating the database. Then we are running the script which populates your database objects, CreateSchema.sql. All file locations are relative to the build folder, however you can enter any paths you wish.

db.tsqlt.install: This section installs tSQLt to your test database.

db.tests.install: Here we put your test cases into the database from the test/TestCases.sql file.

db.tests.run: tSQLt.RunAll executes all of the tests. See Creating and Running Test Cases in tSQLt for other options on running test cases.

db.tests.output: Queries the tSQLt.TestResult table in XML format and stores the results in test/results/TestResults.xml. Here is an example of GetTestResults.sql script which could be used here:

:XML ON
EXEC [tSQLt].[XmlResultFormatter];

Note that the :XML ON command used here is a special sqlcmd directive which allows long XML strings to be output. tSQLt.XmlResultFormatter retrieves the test results as an XML document compatible with CruiseControl.

db.tests.check.results: This last step in the build, causes the build to fail if any test cases have failed or caused an error.

Merging the Test Case Results
Finally, we’ll go back to the config.xml in the CruiseControl installation folder. We need to add a log element to our config script. The log element merges the output of the test results with the CruiseControl log. This is our final project configuration in config.xml:

<project name="MyDatabaseProject">
    <listeners>
        <currentbuildstatuslistener file="logs/${project.name}/status.txt"/>
    </listeners>

    <modificationset quietperiod="30">
        <filesystem folder="C:/projects/${project.name}"/>
    </modificationset>

    <schedule interval="300">
        <ant anthome="apache-ant-1.7.0" buildfile="c:/projects/${project.name}/build/build.xml">
            <property name="db.server" value="localhost"/>
            <property name="db.name" value="MyUnitTestDatabase"/>
        </ant>
    </schedule>

    <log>
        <merge dir="c:/projects/${project.name}/build/test/results"/>
    </log>
</project>

Summary
Automating your build process using a tool such as CruiseControl to execute your tests can provide you with constant feedback on your development. This section has demonstrated a very simple way of configuring CruiseControl to work with tSQLt. Your actual build script is likely to also include steps to compile business logic code written in another logic and execute other types of test cases. Having access to your database level tests in your build process is important to maintaining database quality.

 

Overview
The ResultSetFilter procedure provides the ability to retrieve a single result set from a statement which produces multiple result sets. SQL Server comes installed with many stored procedures which return multiple result sets, such as sp_monitor, sp_spaceused and sp_help (when used with certain parameters). It is also common that developers will want to write their own stored procedures which return multiple result sets.

Examples
The most common need to use ResultSetFilter is when you want to capture a specific result set for further processing by another stored procedure. Supposed for example, you wanted to process the data from the second result set of sp_spaceused. This result set contains information on how reserved space in the database is utilized (for data, indexes and unused reserved space).

First we’ll create a table to store the second result set, and then we’ll populate it using the ResultSetFilter. The number 2 that we pass as the first parameter to ResultSetFilter tells us that we want the second result set returned from the stored procedure.

CREATE TABLE #ReservedSpaceUsed (
    reserved VARCHAR(100),
    data VARCHAR(100),
    index_size VARCHAR(100),
    unused VARCHAR(100)
);

INSERT INTO #ReservedSpaceUsed
EXEC tSQLt.ResultSetFilter 2, 'EXEC sp_spaceused';

SELECT * FROM #ReservedSpaceUsed;

Implications to Testing with tSQLt
When you are testing a stored procedure which returns multiple result sets, you will want to use ResultSetFilter to choose an individual result set and store it in a table. The results can then be used with tSQLt.AssertEqualsTable (see Example 3 in the tSQLt Tutorial).

Limitations
Each time you call ResultSetFilter, the procedure you pass as a command is executed. For example, suppose you want to capture both result sets from sp_spaceused with ResultSetFilter:

CREATE TABLE #DatabaseSize (
    database_name nvarchar(128),
    database_size varchar(18),
    unallocated_space varchar(18)
);

CREATE TABLE #ReservedSpaceUsed (
    reserved VARCHAR(18),
    data VARCHAR(18),
    index_size VARCHAR(18),
    unused VARCHAR(18)
);

INSERT INTO #DatabaseSize
EXEC tSQLt.ResultSetFilter 1, 'EXEC sp_spaceused';

INSERT INTO #ReservedSpaceUsed
EXEC tSQLt.ResultSetFilter 2, 'EXEC sp_spaceused';

SELECT * FROM #DatabaseSize;
SELECT * FROM #ReservedSpaceUsed;

Since the procedure is executed twice, it is possible that the size of the database has changed between each call and the two results are no longer in sync. This has a greater impact if you are testing a procedure which causes a state change (changing the data or schema of the database) or the data is time-sensitive.

Summary
tSQLt.ResultSetFilter provide the ability to choose a single result set from a stored procedure which returns multiple result sets. This allows test cases to be written for this type of stored procedure.

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;