tSQLt QUICK REFERENCE GUIDE
tSQLt Framework Assertions Isolating Dependencies
tSQLt is the open source database unit testing AssertEquals FakeTable
framework used in SQL Test. tSQLt is compatible Compares two values for equality. Replaces a table with a fake table, without data and
with SQL Server 2005 (service pack 2 required) tSQLt.AssertEquals [@Expected = ] expected value constraints.
and above on all editions. , [@Actual = ] actual value tSQLt.FakeTable [@TableName = ] 'table name'
[, [@Message = ] 'message' ] , [[@SchemaName = ] 'schema name']
, [[@Identity = ] 'preserve identity']
tSQLt allows you to implement unit tests in T-SQL , [[@ComputedColumns = ] 'preserve computed columns']
AssertEqualsString
as stored procedures. The framework itself is , [[@Defaults = ] 'preserve default constraints']
Compares two string values for equality.
a collection of objects that instal into a schema
named tSQLt. The following tSQLt features
tSQLt.AssertEqualsString [@Expected = ] expected value ApplyConstraint
, [@Actual = ] actual value
make it easier to create and manage unit tests. Adds back constraints to a faked table so they can
[, [@Message = ] 'message' ]
be tested independently.
tSQLt.ApplyConstraint [@TableName = ] 'table name'
Key Points AssertEqualsTable , [@ConstraintName = ] 'constraint name'
Compares the contents of two tables for equality. , [@SchemaName = ] 'schema name'
Tests are automatically run within transactions tSQLt.AssertEqualsTable [@Expected = ] 'expected table name'
– this keeps tests independent and removes , [@Actual = ] 'actual table name' SpyProcedure
the need for clean-up code, as all changes are [, [@FailMsg = ] 'message' ] Replaces stored procedure functionality with logging.
automatically rolled back. tSQLt.SpyProcedure [@ProcedureName = ] 'procedure name'
AssertObjectExists [, [@CommandToExecute = ] 'command' ]
Tests can be grouped together within a schema Checks to see if an object with the specified name
exists in the database.
– allowing you to organize your tests and use AdventureWorks Example Test
tSQLt.AssertObjectExists [@ObjectName = ] 'object name'
common setup methods. [, [@Message = ] 'message' ] CREATE PROCEDURE [MyNewTestClass].[test update employee]
AS
BEGIN
Results can be output as text or JUnit XML AssertResultSetsHaveSameMetaData EXEC tSQLt.FakeTable 'HumanResources.Employee';
– making it easier to integrate with a continuous Compares the meta data (column names and
INSERT INTO HumanResources.Employee (EmployeeID, Gender)
VALUES (0, 'M');
integration tool. properties) of results for two commands. EXEC HumanResources.uspUpdateEmployeePersonalInfo
tSQLt.AssertResultSetsHaveSameMetaData @EmployeeID = 0,
Mocking features to fake tables and views, and [@expectedCommand = ] 'expected command'
@NationalIDNumber = NULL,
@BirthDate = NULL,
create stored procedure spies – allowing you to @MaritalStatus = NULL,
@Gender = 'F';
isolate the code which you are testing. Fail
Simply fails a test case with the specified failure DECLARE @ActualGender CHAR(1);
SET @ActualGender = (SELECT Gender FROM HumanResources.Employee);
message.
EXEC tSQLt.AssertEquals @Expected = 'F', @Actual = @ActualGender;
tSQLt.Fail [ [@Message0 = ] message part ] END;
