tSQLt

https://tsqlt.org/
Admin
tSQLt is an open-source database unit testing framework for SQL Server that enables developers to...
Tokens:41,388
Snippets:489
Trust Score:9.4
Update:6 months ago
Show doc for...
Tokens:
Raw
### Run All tSQLt Example Tests

Source: https://tsqlt.org/user-guide/quick-start

Execute this script to run all example tests included with tSQLt. This helps verify the installation and provides an overview of test results, including successes and failures.

```sql
EXEC tSQLt.RunAll
```

--------------------------------

### Create a SetUp Procedure for a Test Class

Source: https://tsqlt.org/130/creating-and-running-test-cases-in-tsqlt

Create a stored procedure named 'SetUp' within your test class schema to execute setup logic before each test case in that class.

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

--------------------------------

### DropClass Usage Example

Source: https://tsqlt.org/user-guide/test-creation-and-execution/dropclass

Demonstrates creating a test class and subsequently dropping it.

```sql
EXEC tSQLt.NewTestClass 'testFinancialApp';
GO

EXEC tSQLt.DropClass 'testFinancialApp';
GO
```

--------------------------------

### Get tSQLt Version and Build Information

Source: https://tsqlt.org/710/tsqlt-v1-0-5686-18945-release-notes

Use tSQLt.Info() to retrieve the version and build of the installed SQL Server Instance. The output includes version, CLR version, SQL version, and build number.

```sql
SELECT * FROM tSQLt.Info() AS I;
```

--------------------------------

### Creating a test class and test case

Source: https://tsqlt.org/user-guide/test-creation-and-execution/newtestclass

Example showing the creation of a test class and a corresponding test procedure using SpyProcedure and assertEqualsTable.

```sql
EXEC tSQLt.NewTestClass 'testFinancialApp';
GO

CREATE PROCEDURE testFinancialApp.[test that SalesReport calls HistoricalReport when @showHistory = 1]
AS
BEGIN
-------Assemble
    EXEC tSQLt.SpyProcedure 'FinancialApp.HistoricalReport';
    EXEC tSQLt.SpyProcedure 'FinancialApp.CurrentReport';

-------Act
    EXEC FinancialApp.SalesReport 'USD', @showHistory = 1;

    SELECT currency
      INTO actual
      FROM FinancialApp.HistoricalReport_SpyProcedureLog;

-------Assert HistoricalReport got called with right parameter
    SELECT currency
      INTO expected
      FROM (SELECT 'USD') AS ex(currency);

    EXEC tSQLt.assertEqualsTable 'actual', 'expected';

-------Assert CurrentReport did not get called
    IF EXISTS (SELECT 1 FROM FinancialApp.CurrentReport_SpyProcedureLog)
       EXEC tSQLt.Fail 'SalesReport should not have called CurrentReport when @showHistory = 1';
END;
GO
```

--------------------------------

### tSQLt.RunAll

Source: https://tsqlt.org/user-guide/test-creation-and-execution/runall

Executes all tests in all test classes. If a SetUp stored procedure exists in the test class schema, it is executed before each test case. Test case stored procedures must start with 'test'. A summary of test execution is displayed.

```APIDOC
## tSQLt.RunAll

### Description
Executes all tests in all test classes created with tSQLt.NewTestClass in the current database. If the test class schema contains a stored procedure called SetUp, it is executed before calling each test case. The name of each test case stored procedure must begin with ‘test’. RunAll displays a test case summary.

### Method
EXEC

### Endpoint
tSQLt.RunAll

### Parameters
None

### Request Example
```sql
EXEC tSQLt.RunAll;
```

### Response
#### Success Response (0)
Returns 0 on successful execution.

#### Error Raised
Raises an error containing the test case statistics if any test fails or errors. For example:
```
Msg 50000, Level 16, State 10, Line 1
Test Case Summary: 117 test case(s) executed, 116 succeeded, 1 failed, 0 errored.
```
```

--------------------------------

### Install External Access Key for tSQLt

Source: https://tsqlt.org/author/sebastian/page/3

Installs necessary objects in the master database to enable tSQLt to execute with EXTERNAL_ACCESS permissions without requiring the database to be TRUSTWORTHY.

```sql
tSQLt.InstallExternalAccessKey
```

--------------------------------

### Faking a table for view testing

Source: https://tsqlt.org/user-guide/isolating-dependencies/faketable

Example demonstrating how to use FakeTable to remove constraints from a table, allowing for easy data insertion during a test case.

```sql
CREATE PROCEDURE testFinancialApp.[test that ConvertCurrencyUsingLookup converts using conversion rate in CurrencyConversion table]
AS
BEGIN
    DECLARE @expected MONEY; SET @expected = 3.2;
    DECLARE @actual MONEY;
    DECLARE @amount MONEY; SET @amount = 2.00;
    DECLARE @sourceCurrency CHAR(3); SET @sourceCurrency = 'EUR';
    DECLARE @destCurrency CHAR(3); SET @destCurrency = 'USD';

------Fake Table
    EXEC tSQLt.FakeTable 'FinancialApp.CurrencyConversion';

    INSERT INTO FinancialApp.CurrencyConversion (id, SourceCurrency, DestCurrency, ConversionRate)
                                         VALUES (1, @sourceCurrency, @destCurrency, 1.6);
------Execution
    SELECT @actual = amount FROM FinancialApp.ConvertCurrencyUsingLookup(@sourceCurrency, @destCurrency, @amount);

------Assertion
    EXEC tSQLt.assertEquals @expected, @actual;
END;
GO
```

--------------------------------

### Example: AssertEqualsTable for View Results

Source: https://tsqlt.org/user-guide/assertions/assertequalstable

Demonstrates using AssertEqualsTable to compare the output of a view against a predefined expected dataset. Ensure 'expected' and 'actual' tables are dropped if they exist before running.

```sql
CREATE PROCEDURE testFinancialApp.[test that Report gets sales data with converted currency]
AS
BEGIN
    IF OBJECT_ID('actual') IS NOT NULL DROP TABLE actual;
    IF OBJECT_ID('expected') IS NOT NULL DROP TABLE expected;

------Fake Table
    EXEC tSQLt.FakeTable 'FinancialApp', 'CurrencyConversion';
    EXEC tSQLt.FakeTable 'FinancialApp', 'Sales';

    INSERT INTO FinancialApp.CurrencyConversion (id, SourceCurrency, DestCurrency, ConversionRate)
                                         VALUES (1, 'EUR', 'USD', 1.6);
    INSERT INTO FinancialApp.CurrencyConversion (id, SourceCurrency, DestCurrency, ConversionRate)
                                         VALUES (2, 'GBP', 'USD', 1.2);

    INSERT INTO FinancialApp.Sales (id, amount, currency, customerId, employeeId, itemId, date)
                                         VALUES (1, '1050.00', 'GBP', 1000, 7, 34, '1/1/2007');
    INSERT INTO FinancialApp.Sales (id, amount, currency, customerId, employeeId, itemId, date)
                                         VALUES (2, '4500.00', 'EUR', 2000, 19, 24, '1/1/2008');

------Execution
    SELECT amount, currency, customerId, employeeId, itemId, date
      INTO actual
      FROM FinancialApp.Report('USD');

------Assertion
    CREATE TABLE expected (
	    amount MONEY,
	    currency CHAR(3),
	    customerId INT,
	    employeeId INT,
	    itemId INT,
	    date DATETIME
    );

	INSERT INTO expected (amount, currency, customerId, employeeId, itemId, date) SELECT 1260.00, 'USD', 1000, 7, 34, '2007-01-01';
	INSERT INTO expected (amount, currency, customerId, employeeId, itemId, date) SELECT 7200.00, 'USD', 2000, 19, 24, '2008-01-01';

	EXEC tSQLt.AssertEqualsTable 'expected', 'actual';
END;
GO

```

--------------------------------

### Test Case: AssertEmptyTable for View Results

Source: https://tsqlt.org/user-guide/assertions/assertemptytable

This example demonstrates using AssertEmptyTable within a tSQLt test case to verify that a report view generates no rows when its base tables are empty. It includes setup for faking tables, executing the view, and asserting the expected empty result.

```sql
CREATE PROCEDURE testFinancialApp.[test that Report generates no rows if base tables are empty]
AS
BEGIN
    IF OBJECT_ID('actual') IS NOT NULL DROP TABLE actual;

------Fake Table
    EXEC tSQLt.FakeTable 'FinancialApp', 'CurrencyConversion';
    EXEC tSQLt.FakeTable 'FinancialApp', 'Sales';

------Execution
    SELECT amount, currency, customerId, employeeId, itemId, date
      INTO actual
      FROM FinancialApp.Report('USD');

------Assertion
    EXEC tSQLt.AssertEmptyTable 'actual';
END;
GO
```

--------------------------------

### Test Function Result with AssertLike

Source: https://tsqlt.org/user-guide/assertions/assertlike

This example demonstrates using AssertLike to validate the output of a function against an expected pattern. Ensure the function and test procedure are correctly defined.

```sql
CREATE PROC TestPerson.[test FormatName concatenates names correctly]
AS
BEGIN
    DECLARE @actual NVARCHAR(MAX);

    SELECT @actual = person.FormatName('John', 'Smith');

    EXEC tSQLt.AssertLike 'John%Smith', @actual;
END;

```

--------------------------------

### Use FakeFunction to isolate a view test

Source: https://tsqlt.org/user-guide/isolating-dependencies/fakefunction

Example demonstrating how to replace a complex scalar function with a simple fake version to test a view's output.

```sql
EXEC tSQLt.NewTestClass 'SalesAppTests';
GO

CREATE FUNCTION SalesAppTests.Fake_ComputeCommission (
    @EmployeeId INT, 
    @RevenueFromSales DECIMAL(10,4)
)
RETURNS DECIMAL(10,4)
AS
BEGIN
  RETURN 1234.5678;
END;
GO

CREATE PROCEDURE SalesAppTests.[test SalesReport returns revenue and commission]
AS
BEGIN
-------
Assemble
    EXEC tSQLt.FakeFunction 'SalesApp.ComputeCommission', 'SalesAppTests.Fake_ComputeCommission';
    EXEC tSQLt.FakeTable 'SalesApp.Employee';
    EXEC tSQLT.FakeTable 'SalesApp.Sales';

    INSERT INTO SalesApp.Employee (EmployeeId) VALUES (1);
    INSERT INTO SalesApp.Sales (EmployeeId, SaleAmount) VALUES (1, 10.1);
    INSERT INTO SalesApp.Sales (EmployeeId, SaleAmount) VALUES (1, 20.2);

-------
Act
    SELECT EmployeeId, RevenueFromSales, Commission
      INTO SalesAppTests.Actual
      FROM SalesApp.SalesReport;

-------
Assert
    SELECT TOP(0) *
      INTO SalesAppTests.Expected
      FROM SalesAppTests.Actual;

    INSERT INTO SalesAppTests.Expected (EmployeeId, RevenueFromSales, Commission) 
      VALUES (1, 30.3, 1234.5678);

    EXEC tSQLt.AssertEqualsTable 'SalesAppTests.Expected', 'SalesAppTests.Actual';
END;
GO
```

--------------------------------

### Test Stored Procedure Creation with AssertObjectExists

Source: https://tsqlt.org/user-guide/assertions/assertobjectexists

This example demonstrates using AssertObjectExists to verify that a stored procedure, 'UpdateMyTable', was created successfully after executing 'TemplateUtil.CreateTableTemplate'.

```tsql
CREATE PROC TestTemplateUtil.[test CreateTableTemplate creates an update stored procedure]
AS
BEGIN
    CREATE TABLE MyTable (i INT);

    EXEC TemplateUtil.CreateTableTemplate 'MyTable';

    EXEC tSQLt.AssertObjectExists 'UpdateMyTable';
END;
```

--------------------------------

### AssertEqualsString Examples with Various Inputs

Source: https://tsqlt.org/user-guide/assertions/assertequalsstring

Demonstrates AssertEqualsString behavior with different string inputs, including case sensitivity, Unicode literals, and NULL values. Note that NULL is considered equal to NULL, but not equal to any non-NULL string.

```sql
EXEC tSQLt.AssertEqualsString 'hello', 'hello'; -- pass
```

```sql
EXEC tSQLt.AssertEqualsString N'goodbye', N'goodbye'; -- pass
```

```sql
EXEC tSQLt.AssertEqualsString 'hello', N'hello'; - pass (values are compared as NVARCHAR(MAX)
```

```sql
EXEC tSQLt.AssertEqualsString 'hello', NULL; -- fail
```

```sql
EXEC tSQLt.AssertEqualsString NULL, 'hello'; -- fail
```

```sql
EXEC tSQLt.AssertEqualsString NULL, NULL; -- pass
```

--------------------------------

### Execute All tSQLt Tests

Source: https://tsqlt.org/user-guide/test-creation-and-execution/runall

Use tSQLt.RunAll to execute all tests in all test classes. Ensure that test case stored procedures are named starting with 'test'. A SetUp procedure in the test class schema will be executed before each test.

```sql
tSQLt.RunAll
```

--------------------------------

### Validate View Metadata with AssertResultSetsHaveSameMetaData

Source: https://tsqlt.org/user-guide/assertions/assertresultsetshavesamemetadata

Example demonstrating how to verify that a view's column structure matches an expected schema definition.

```sql
CREATE PROC TestHumanResources.[test EmployeeAgeReport has appropriate meta data]
AS
BEGIN
    EXEC tSQLt.AssertResultSetsHaveSameMetaData
        'SELECT CAST(''A'' AS VARCHAR(1000)) AS name, CAST(30 AS SMALLINT) AS age',
        'SELECT name, age FROM HumanResources.EmployeeAgeReport';
END;
```

--------------------------------

### tSQLt Test Execution Summary Output

Source: https://tsqlt.org/user-guide/quick-start

This is an example of the output you will see after running tSQLt.RunAll, detailing the status of each test case executed. It includes a summary of successes, failures, and errors.

```text
[AcceleratorTests].[test ready for experimentation if 2 particles] failed: Expected: <1> but was: <0>
 
+----------------------+
|Test Execution Summary|
+----------------------+
 
|No|Test Case Name                                                                                            |Result |
+--+----------------------------------------------------------------------------------------------------------+-------+
|1 |[AcceleratorTests].[test a particle is included only if it fits inside the boundaries of the rectangle]   |Success|
|2 |[AcceleratorTests].[test a particle within the rectangle is returned with an Id, Point Location and Value]|Success|
|3 |[AcceleratorTests].[test a particle within the rectangle is returned]                                     |Success|
|4 |[AcceleratorTests].[test email is not sent if we detected something other than higgs-boson]               |Success|
|5 |[AcceleratorTests].[test email is sent if we detected a higgs-boson]                                      |Success|
|6 |[AcceleratorTests].[test foreign key is not violated if Particle color is in Color table]                 |Success|
|7 |[AcceleratorTests].[test foreign key violated if Particle color is not in Color table]                    |Success|
|8 |[AcceleratorTests].[test no particles are in a rectangle when there are no particles in the table]        |Success|
|9 |[AcceleratorTests].[test status message includes the number of particles]                                 |Success|
|10|[AcceleratorTests].[test we are not ready for experimentation if there is only 1 particle]                |Success|
|11|[AcceleratorTests].[test ready for experimentation if 2 particles]                                        |Failure|
-------------------------------------------------------------------------------
Msg 50000, Level 16, State 10, Line 1
Test Case Summary: 11 test case(s) executed, 10 succeeded, 1 failed, 0 errored.
-------------------------------------------------------------------------------
```

--------------------------------

### Call T-SQL Function

Source: https://tsqlt.org/146/database-test-driven-development

Example of how to call the FnNum function to generate a list of numbers.

```sql
SELECT no FROM dbo.FnNum(5)
```

--------------------------------

### Select Data into Actual Table

Source: https://tsqlt.org/category/articles/page/2

Selects data from the 'AvailableHoursSummary' view into the 'actual' table. This is typically done as part of a test setup to capture the current state of the data.

```sql
SELECT firstName, lastName, officeHours INTO OfficeHoursTests.actual
    FROM dbo.AvailableHoursSummary
```

--------------------------------

### AssertEquals in a test procedure

Source: https://tsqlt.org/user-guide/assertions/assertequals

Example of using AssertEquals to verify the output of a function within a tSQLt test case.

```sql
CREATE PROCEDURE testFinancialApp.[test that ConvertCurrencyUsingLookup converts using conversion rate in CurrencyConversion table]
AS
BEGIN
    DECLARE @expected MONEY; SET @expected = 3.2;
    DECLARE @actual MONEY;
    DECLARE @amount MONEY; SET @amount = 2.00;
    DECLARE @sourceCurrency CHAR(3); SET @sourceCurrency = 'EUR';
    DECLARE @destCurrency CHAR(3); SET @destCurrency = 'USD';

------Fake Table
    EXEC tSQLt.FakeTable 'FinancialApp', 'CurrencyConversion';

    INSERT INTO FinancialApp.CurrencyConversion (id, SourceCurrency, DestCurrency, ConversionRate)
                                         VALUES (1, @sourceCurrency, @destCurrency, 1.6);
------Execution
    SELECT @actual = amount FROM FinancialApp.ConvertCurrencyUsingLookup(@sourceCurrency, @destCurrency, @amount);

------Assertion
    EXEC tSQLt.assertEquals @expected, @actual;
END;
GO
```

--------------------------------

### AssertEqualsTable Failure Message Example

Source: https://tsqlt.org/user-guide/assertions/assertequalstable

Illustrates the format of the failure message generated by AssertEqualsTable when expected and actual tables do not match. Symbols '<', '>', and '=' indicate row comparison results.

```text
failed: unexpected/missing resultset rows!
|_m_|col1|col2|col3|
+---+----+----+----+
|<  |2   |B   |b   |
|<  |3   |C   |c   |
|=  |1   |A   |a   |
|>  |3   |X   |c   |


```

--------------------------------

### Test IsDiskSpaceTooLow Procedure with SpyProcedure

Source: https://tsqlt.org/user-guide/isolating-dependencies/spyprocedure

Use tSQLt.SpyProcedure to return hard-coded output parameter values for a procedure. This example tests IsDiskSpaceTooLow by faking the return value of GetDiskSpace.

```sql
CREATE PROCEDURE DiskUtil.GetDiskSpace @DiskSpace INT OUT
AS
BEGIN
    -- This procedure does something to return the disk space as @DiskSpace output parameter
END
GO

CREATE PROCEDURE DiskUtil.IsDriveSpaceTooLow
AS
BEGIN
    DECLARE @DiskSpace INT;
    EXEC DiskUtil.GetDiskSpace @DiskSpace = @DiskSpace OUT;

    IF @DiskSpace < 512
        RETURN -1;
    ELSE
        RETURN 0;
END;
GO

CREATE PROCEDURE testDiskUtil.[test IsDriveSpaceTooLow returns -1 if drive space is less than 512 MB]
AS
BEGIN
    EXEC tSQLt.SpyProcedure 'DiskUtil.GetDiskSpace', 'SET @DiskSpace = 511';

    DECLARE @ReturnValue INT;
    EXEC @ReturnValue = DiskUtil.IsDriveSpaceTooLow;

    EXEC tSQLt.AssertEquals -1, @ReturnValue;
END
GO

```

--------------------------------

### Test Trigger Isolation with ApplyTrigger

Source: https://tsqlt.org/user-guide/isolating-dependencies/applytrigger

This example demonstrates how to use ApplyTrigger in conjunction with FakeTable to isolate and test the 'AuditInserts' trigger on the 'Registry.Student' table. It sets up a test class, fakes the relevant tables, applies the trigger, simulates an insert, and asserts the expected outcome in the 'Logs.Audit' table.

```sql
EXEC tSQLt.NewTestClass 'AuditTests';
GO

CREATE PROCEDURE AuditTests.[test inserting record into Student table creates Audit record]
AS
BEGIN
  EXEC tSQLt.FakeTable 'Registry.Student';
  EXEC tSQLt.FakeTable @TableName = 'Logs.Audit';
  EXEC tSQLt.ApplyTrigger 'Registry.Student', 'AuditInserts';
  
  INSERT INTO Registry.Student (StudentId) VALUES (1);
  
  SELECT LogMessage
  INTO #Actual
  FROM Logs.Audit;
  
  SELECT TOP(0) *
  INTO #Expected
  FROM #Actual;
  
  INSERT INTO #Expected
  VALUES('Student record created, id = 1');
  
  EXEC tSQLt.AssertEqualsTable '#Expected','#Actual';
END;
GO
```

--------------------------------

### Verify Error Handling in Test

Source: https://tsqlt.org/user-guide/expectations/expectexception

Example of using tSQLt.ExpectException within a test procedure to verify that a specific error is raised when calling a target procedure.

```sql
CREATE PROCEDURE PurgeTableTests.[test dbo.PurgeTable rejects not existing table]
AS
BEGIN
  EXEC tSQLt.ExpectException @Message = 'Table dbo.DoesNotExist not found.', @ExpectedSeverity = 16, @ExpectedState = 10;
  EXEC dbo.PurgeTable @TableName='dbo.DoesNotExist';
END;
GO
```

--------------------------------

### Ant Build Script for tSQLt Tests

Source: https://tsqlt.org/author/dennis/page/5

A comprehensive Ant build script that orchestrates the entire tSQLt testing process, from database setup to test execution and result collection.

```xml
<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 \"IF EXISTS(SELECT 1 FROM sys.databases WHERE name='${db.name}') DROP DATABASE ${db.name}; CREATE DATABASE ${db.name};\""/>
            <arg value="sqlcmd -S ${db.server} -E -I -i \"CreateSchema.sql\""/>
        </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 \"test/tSQLt.class.sql\""/>
        </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 \"test/TestCases.sql\""/>
        </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 \"EXEC tSQLt.RunAll;\""/>
        </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 \"test/GetTestResults.sql\" -o \"test/results/TestResults.xml\""/>
        </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 \":EXIT(SELECT COUNT(*) FROM tSQLt.TestResult WHERE Result != 'Success')\""/>
        </exec>
    </target>
</project>
```

--------------------------------

### Define Ant Build Script for tSQLt

Source: https://tsqlt.org/177/integrating-tsqlt-with-cruise-control

An Ant build script that automates database recreation, tSQLt installation, test execution, and result reporting via sqlcmd.

```xml
<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>
```

--------------------------------

### Test SalesReport Procedure with SpyProcedure

Source: https://tsqlt.org/user-guide/isolating-dependencies/spyprocedure

Use tSQLt.SpyProcedure to record parameters passed to a procedure. This example tests that the SalesReport procedure correctly handles a parameter and calls either HistoricalReport or CurrentReport.

```sql
CREATE PROCEDURE testFinancialApp.[test that SalesReport calls HistoricalReport when @showHistory = 1]
AS
BEGIN
-------Assemble
    EXEC tSQLt.SpyProcedure 'FinancialApp.HistoricalReport';
    EXEC tSQLt.SpyProcedure 'FinancialApp.CurrentReport';

-------Act
    EXEC FinancialApp.SalesReport 'USD', @showHistory = 1;

    SELECT currency
      INTO actual
      FROM FinancialApp.HistoricalReport_SpyProcedureLog;

-------Assert HistoricalReport got called with right parameter
    SELECT currency
      INTO expected
      FROM (SELECT 'USD') ex(currency);

    EXEC tSQLt.AssertEqualsTable 'actual', 'expected';

-------Assert CurrentReport did not get called
    IF EXISTS (SELECT 1 FROM FinancialApp.CurrentReport_SpyProcedureLog)
       EXEC tSQLt.Fail 'SalesReport should not have called CurrentReport when @showHistory = 1';
END;
GO

```

--------------------------------

### Execute a Specific tSQLt Test Case

Source: https://tsqlt.org/category/articles

Use EXEC tSQLt.Run 'SchemaName.[Test Case Name]'; to execute a single, qualified test case. This also runs the test class's SetUp procedure if it exists.

```sql
EXEC tSQLt.Run 'MyTestClass.[test addNumbers computes 2 plus 2 equals 4]';
```

--------------------------------

### AssertLike with Various Patterns and Values

Source: https://tsqlt.org/user-guide/assertions/assertlike

Illustrates different scenarios for AssertLike, including successful and failed matches with wildcard characters, and handling of NULL values. Note that NULL compared to NULL passes.

```sql
EXEC tSQLt.AssertLike 'hello', 'hello'; -- pass
EXEC tSQLt.AssertLike '%el%', 'hello'; - pass
EXEC tSQLt.AssertLike 'h_llo', 'hello'; - pass
EXEC tSQLt.AssertLike '%oo%', 'hello'; - fail

EXEC tSQLt.AssertLike 'hello', NULL; -- fail
EXEC tSQLt.AssertLike NULL, 'hello'; -- fail
EXEC tSQLt.AssertLike NULL, NULL; -- pass

```

--------------------------------

### Execute tSQLt Test Cases

Source: https://tsqlt.org/author/dennis/page/6

Demonstrates various methods to run tests, including running all tests, specific classes, individual cases, or repeating the last execution.

```sql
-- 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;
```

--------------------------------

### Example: Assert No Error Raised with tSQLt.ExpectNoException

Source: https://tsqlt.org/user-guide/expectations/expectnoexception

This example demonstrates how to use tSQLt.ExpectNoException to assert that the `dbo.PurgeTableIfExists` stored procedure does not raise an error when called with a non-existent table name. The test will fail if an error is thrown.

```sql
CREATE PROCEDURE PurgeTableTests.[test dbo.PurgeTableIfExists ignores not existing table]
AS
BEGIN
  EXEC tSQLt.ExpectNoException;
  EXEC dbo.PurgeTableIfExists @TableName='dbo.DoesNotExist';
END;
GO
```

--------------------------------

### Create and Run a Test Class

Source: https://tsqlt.org/user-guide/tsqlt-tutorial

Use tSQLt.NewTestClass to group tests into a schema and tSQLt.Run to execute all tests within that class.

```sql
EXEC tSQLt.NewTestClass 'testFinancialApp';
GO

CREATE PROCEDURE testFinancialApp.[test that ConvertCurrency converts using given conversion rate]
AS
BEGIN
    DECLARE @actual MONEY;
    DECLARE @rate DECIMAL(10,4); SET @rate = 1.2;
    DECLARE @amount MONEY; SET @amount = 2.00;

    SELECT @actual = FinancialApp.ConvertCurrency(@rate, @amount);

    DECLARE @expected MONEY; SET @expected = 2.4;   --(rate * amount)
    EXEC tSQLt.AssertEquals @expected, @actual;

END;
GO
```

```sql
EXEC tSQLt.Run 'testFinancialApp';
```

--------------------------------

### AssertEquals usage variations

Source: https://tsqlt.org/user-guide/assertions/assertequals

Demonstrates how AssertEquals handles various data types and equality scenarios.

```sql
    EXEC tSQLt.AssertEquals 12345.6789, 12345.6789; -- pass
    EXEC tSQLt.AssertEquals 'hello', 'hello'; -- pass
    EXEC tSQLt.AssertEquals N'hello', N'hello'; -- pass

    DECLARE @datetime DATETIME; SET @datetime = CAST('12-13-2005' AS DATETIME);
    EXEC tSQLt.AssertEquals @datetime, @datetime; -- pass

    DECLARE @bit BIT; SET @bit = CAST(1 AS BIT);
    EXEC tSQLt.AssertEquals @bit, @bit; -- pass

    EXEC tSQLt.AssertEquals NULL, NULL; -- pass
    EXEC tSQLt.AssertEquals 17, NULL; -- fail
    EXEC tSQLt.AssertEquals NULL, 17; -- fail

    EXEC tSQLt.AssertEquals 12345.6789, 54321.123; -- fail
    EXEC tSQLt.AssertEquals 'hello', 'goodbye'; -- fail

    DECLARE @datetime1 DATETIME; SET @datetime1 = CAST('12-13-2005' AS DATETIME);
    DECLARE @datetime2 DATETIME; SET @datetime2 = CAST('07-19-2005' AS DATETIME);
    EXEC tSQLt.AssertEquals @datetime1, @datetime2; -- fail

    DECLARE @bit1 BIT; SET @bit1 = CAST(1 AS BIT);
    DECLARE @bit2 BIT; SET @bit2 = CAST(1 AS BIT);
    EXEC tSQLt.AssertEquals @bit1, @bit2; -- pass
```

--------------------------------

### Execute tSQLt tests

Source: https://tsqlt.org/user-guide/test-creation-and-execution/run

Demonstrates various ways to invoke tSQLt.Run, including executing a full test class, a specific test case, or repeating the previous execution.

```sql
-- 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;
```

--------------------------------

### Remove External Access Key for tSQLt

Source: https://tsqlt.org/author/sebastian/page/3

Removes objects from the master database that were installed by tSQLt.InstallExternalAccessKey, revoking EXTERNAL_ACCESS permissions.

```sql
tSQLt.RemoveExternalAccessKey
```

--------------------------------

### Configure tSQLt RunAll Shortcut

Source: https://tsqlt.org/user-guide/tsqlt-keyboard-shortcuts

Use this command in the SQL Server Management Studio keyboard options to trigger all tests via a hot-key.

```sql
EXEC tSQLt.RunAll; --
```

--------------------------------

### Insert Test Data for Office Hours

Source: https://tsqlt.org/category/articles/page/2

Use INSERT statements to populate the 'expected' table with sample office hours data for testing purposes. This sets up the baseline for comparison.

```sql
INSERT INTO OfficeHoursTests.expected
        (firstName, lastName, officeHours)
    VALUES
        ('George', 'Franklin',
         'Monday 1:00 PM-2:00 PM; Tuesday 4:00 PM-5:00 PM;');
    INSERT INTO OfficeHoursTests.expected
        (firstName, lastName, officeHours)
    VALUES
        ('Sarah', 'McGuire',
         'Monday 3:00 PM-4:00 PM; Wednesday 10:00 AM-11:00 AM; Friday 1:00 PM-3:00 PM;');
```

--------------------------------

### Isolate Tests with FakeTable

Source: https://tsqlt.org/user-guide/tsqlt-tutorial

Replace a table with a constraint-free version using tSQLt.FakeTable to simplify data setup. The original table is automatically restored after the test transaction completes.

```sql
CREATE PROCEDURE testFinancialApp.[test that ConvertCurrencyUsingLookup converts using conversion rate in CurrencyConversion table]
AS
BEGIN
    DECLARE @expected MONEY; SET @expected = 3.2;
    DECLARE @actual MONEY;
    DECLARE @amount MONEY; SET @amount = 2.00;
    DECLARE @sourceCurrency CHAR(3); SET @sourceCurrency = 'EUR';
    DECLARE @destCurrency CHAR(3); SET @destCurrency = 'USD';

------Fake Table
    EXEC tSQLt.FakeTable 'FinancialApp', 'CurrencyConversion';

    INSERT INTO FinancialApp.CurrencyConversion (id, SourceCurrency, DestCurrency, ConversionRate)
                                         VALUES (1, @sourceCurrency, @destCurrency, 1.6);
------Execution
    SELECT @actual = amount FROM FinancialApp.ConvertCurrencyUsingLookup(@sourceCurrency, @destCurrency, @amount);

------Assertion
    EXEC tSQLt.AssertEquals @expected, @actual;
END;
GO
```

--------------------------------

### Create View for Summary Report

Source: https://tsqlt.org/169/refactoring-example-office-hours

Encapsulates the legacy employee office hours retrieval logic into a view to facilitate testing.

```sql
CREATE VIEW dbo.AvailableHoursSummary
AS
	SELECT firstName, lastName, officeHours
	FROM dbo.Employee;
```

--------------------------------

### Create a tSQLt Test Case

Source: https://tsqlt.org/130/creating-and-running-test-cases-in-tsqlt

Define a test case by creating a stored procedure within your test class schema. The procedure name must start with 'test'.

```sql
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
```

--------------------------------

### AssertLike Syntax

Source: https://tsqlt.org/user-guide/assertions/assertlike

The basic syntax for the AssertLike assertion, including optional parameters for message.

```sql
tSQLt.AssertLike [@ExpectedPattern = ] expected pattern
               , [@Actual = ] actual value
              [, [@Message = ] 'message' ]

```

--------------------------------

### Syntax for tSQLt.AssertEquals

Source: https://tsqlt.org/user-guide/assertions/assertequals

The standard syntax for calling the AssertEquals procedure.

```sql
tSQLt.AssertEquals [@expected = ] expected value
                 , [@actual = ] actual value
                [, [@message = ] 'message' ]
```

--------------------------------

### Test AvailableHoursSummary with Initial Schema

Source: https://tsqlt.org/category/articles/page/2

A test case using tSQLt to verify the functionality of the AvailableHoursSummary view against the initial dbo.Employee table structure. It fakes the table, inserts test data, and asserts the output.

```sql
CREATE PROCEDURE OfficeHoursTests.[test_AvailableHoursSummary_Lists_Hours_For_Each_Employee]
AS
BEGIN

    EXEC tSQLt.fakeTable 'dbo', 'Employee';

    INSERT INTO dbo.Employee
        (id, firstName, lastName, officeHours)
    VALUES
        (1, 'George', 'Franklin', 'Mon before noon, Tue btw 4 and 5');
    INSERT INTO dbo.Employee
        (id, firstName, lastName, officeHours)
    VALUES
        (2, 'Sarah', 'McGuire'. 'MWF 11-12');

    CREATE TABLE OfficeHoursTests.expected (
        firstName VARCHAR(50),
        lastName VARCHAR(50),
        officeHours VARCHAR(MAX)
    );

    INSERT INTO OfficeHoursTests.expected
        (firstName, lastName, officeHours)
    VALUES
        ('George', 'Franklin', 'Mon before noon, Tue btw 4 and 5');
    INSERT INTO OfficeHoursTests.expected
        (firstName, lastName, officeHours)
    VALUES
        ('Sarah', 'McGuire', 'MWF 11-12);

    SELECT firstName, lastName, officeHours INTO OfficeHoursTests.actual
    FROM dbo.AvailableHoursSummary

    EXEC tSQLt.assertEqualsTable 'OfficeHoursTests.expected', 'OfficeHoursTests.actual';

END;
GO
```

--------------------------------

### Using Fail to Validate Random Number Range

Source: https://tsqlt.org/user-guide/assertions/fail

An example demonstrating how to use tSQLt.Fail to terminate a test case if a generated random number falls outside the expected range.

```sql
CREATE PROCEDURE testRandom.[test GetRandomInt(1,10) does not produce values less than 1 or greater than 10]
AS
BEGIN
    SET NOCOUNT ON;

    EXEC Random.SeedRandomOnTime;

    DECLARE @numTrials INT; SET @numTrials = 10000;
    DECLARE @i INT; SET @i = 0;
    DECLARE @r INT;

    WHILE @i < @numTrials
    BEGIN
        EXEC Random.GetRandomInt 1, 10, @r OUTPUT;
        IF @r < 1 OR @r > 10
        BEGIN
            EXEC tSQLt.Fail 'Invalid random value returned: ', @r;
        END;

        SET @i = @i + 1;
    END;
END;
GO
```

--------------------------------

### tSQLt CLR Public Key

Source: https://tsqlt.org/author/sebastian/page/3

The public key token and public key for the tSQLt CLR assembly. This is used for signing and security purposes, allowing installation without setting the database to TRUSTWORTHY.

```plaintext
Public Key Token = 0x7722217d36028e4c
Public Key = 0x0602000000240000525341310004000001000100F7D9A45F2B508C2887A8794B053CE5DEB28743B7C748FF545F1F51218B684454B785054629C1417D1D3542B095D80BA171294948FCF978A502AA03240C024746B563BC29B4D8DCD6956593C0C425446021D699EF6FB4DC2155DE7E393150AD6617EDC01216EA93FCE5F8F7BE9FF605AD2B8344E8CC01BEDB924ED06FD368D1D0
```

--------------------------------

### Test Creation and Execution

Source: https://tsqlt.org/full-user-guide

Procedures for managing test classes and executing test suites.

```APIDOC
## Test Management Procedures

### Description
Procedures used to create, rename, and drop test classes, as well as execute tests.

### Methods
- **NewTestClass**: Creates a new test class.
- **DropClass**: Removes an existing test class.
- **RunAll**: Executes all tests in the framework.
- **Run**: Executes a specific test or test class.
- **RenameClass**: Renames an existing test class.
```

--------------------------------

### DropClass Syntax

Source: https://tsqlt.org/user-guide/test-creation-and-execution/dropclass

The basic syntax for calling the DropClass procedure.

```sql
tSQLt.DropClass [@ClassName = ] 'class name'
```

--------------------------------

### Test Dropping a Stored Procedure with AssertObjectDoesNotExist

Source: https://tsqlt.org/user-guide/assertions/assertobjectdoesnotexist

This example demonstrates using AssertObjectDoesNotExist to verify that a stored procedure named 'dbo.MyProcedure' was successfully dropped. It first creates the procedure, then drops it, and finally asserts its non-existence.

```tsql
CREATE PROC TestTemplateUtil.[test DropProcedure drops a stored procedure]
AS
BEGIN
    EXEC('CREATE PROC dbo.MyProcedure AS RETURN 0;');

    EXEC TemplateUtil.DropProcedure 'dbo.MyProcedure';

    EXEC tSQLt.AssertObjectDoesNotExists 'dbo.MyProcedure';
END;
```

--------------------------------

### tSQLt Test Failure Error Message

Source: https://tsqlt.org/user-guide/test-creation-and-execution/runall

When a test case fails or errors, tSQLt.RunAll raises an error containing a summary of the test execution. This example shows the format of the error message for a single failed test.

```sql
Msg 50000, Level 16, State 10, Line 1
Test Case Summary: 117 test case(s) executed, 116 succeeded, 1 failed, 0 errored.
```

--------------------------------

### EXEC tSQLt.NewTestClass

Source: https://tsqlt.org/user-guide/test-creation-and-execution/newtestclass

Creates a new test class schema. If a schema with the same name already exists, it is dropped first.

```APIDOC
## EXEC tSQLt.NewTestClass

### Description
Creates a new test class. A test class is a schema where users can create test case procedures and related objects. If a schema with the same name already exists, it is dropped first.

### Method
EXEC

### Endpoint
tSQLt.NewTestClass

### Parameters
#### Arguments
- **@ClassName** (string) - Required - The name of the test class to be created.

### Response
#### Success Response (0)
- **Return Code** (int) - Returns 0 upon successful execution.

### Request Example
EXEC tSQLt.NewTestClass 'testFinancialApp';
```

--------------------------------

### Execute All tSQLt Test Cases

Source: https://tsqlt.org/category/articles

Use EXEC tSQLt.RunAll; to run all test cases across all test classes. Ensure test classes are created with tSQLt.NewTestClass.

```sql
EXEC tSQLt.RunAll;
```

--------------------------------

### Configure CruiseControl Project

Source: https://tsqlt.org/177/integrating-tsqlt-with-cruise-control

Defines the project structure and schedule for CruiseControl to monitor a filesystem and trigger an Ant build script.

```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>
</project>
```

--------------------------------

### Unit Test for Person Log Trigger

Source: https://tsqlt.org/146/database-test-driven-development

This stored procedure demonstrates a unit test for a trigger on the Person table that logs updates to the PersonLog table. It uses stub records for test data setup and asserts the correctness of the log entries.

```sql
CREATE PROCEDURE dbo.ut_PersLogTrigger
AS
BEGIN

--SETUP-------------------------------------------
    IF OBJECT_ID('actual') IS NOT NULL DROP TABLE actual
    IF OBJECT_ID('expected') IS NOT NULL DROP TABLE expected

    DECLARE @TestStartTime DATETIME

    EXEC dbo.tsu_FakeTable 'PersLog';

--INPUTS------------------------------------------

    EXEC dbo.tsu_StubRecord 'Pers',1
    EXEC dbo.tsu_StubRecord 'Pers',2
    EXEC dbo.tsu_StubRecord 'Pers',5
    EXEC dbo.tsu_StubRecord 'Pers',6

--EXECUTE-----------------------------------------

    SET @TestStartTime = GETDATE();

    UPDATE Pers SET IsAlien = 1 WHERE id=1;
    UPDATE Pers SET IsAlien = 1 WHERE id=5;

    UPDATE Pers SET Weight = 0 WHERE id=5;

--VALIDATE----------------------------------------

    SELECT id,
           CASE WHEN LogDTime BETWEEN @TestStartTime and GETDATE()
                THEN 'Time in range'
                ELSE 'Time out of range'
           END DateCorrect
      INTO actual
      FROM dbo.PersLog

    SELECT Id, DateCorrect
      INTO expected
      FROM (
            SELECT 1, 'Time in range' UNION ALL
            SELECT 5, 'Time in range' UNION ALL
            SELECT 5, 'Time in range'
           )expected(Id, DateCorrect);

    EXEC dbo.tsu_AssertTablesAreEqual 'expected',
            'actual',
            'A.Id = B.Id',
            @@PROCID,
            'Contents of PersLog did not match expectation.'

--CLEANUP-----------------------------------------

END
```
Tokens:
Raw