Unit Testing Framework with tSQLt and Redgate SQLTest

Overview

tSQLt allows you to implement unit tests in T-SQL. Unit testing will help us get to a stage where we are doing continuous integration, test driven development and Agile Development.

Redgate have released a user interface for tSQLt that integrates directly into SSMS
http://www.red-gate.com/products/sql-development/sql-test/

What is tSQLt?

http://tsqlt.org/
tSQLt is a database unit testing framework for Microsoft SQL Server. tSQLt is compatible with SQL Server 2005 (service pack 2 required) and above on all editions.

How to install

1. Go to http://www.red-gate.com/products/sql-development/sql-test/
2. Download the preview version
3. Install on your machine where SSMS is installed (you will have to close SSMS)

Are there an server components installed?

Yes. You will be prompted when you open SSMS. These are the changes it makes to a database you are doing unit testing on..

A Hello World Example

Create a procedure you want to test

USE [tSQLt_Example]
GO
CREATE PROCEDURE [dbo].[getHelloWorld]
 @pass BIT, 
 @HELLO VARCHAR(15) OUTPUT
AS
BEGIN

 /*
  Return Hello World or Goodbye depending on @pass param
 */
    IF @pass = 1
  SET @HELLO = 'Hello World!'
 ELSE
  SET @HELLO = 'Goodbye World!'  
   
END
GO

Create your unit Test stored procedure

In SSMS in the SQL Test window, choose 'New Test...'

Depending what version you are on depends what happens here (anything prior to 2008R2 means you just have the write the sproc, i couldn't get anything else to happen)

USE [tSQLt_Example]
GO
Create PROCEDURE [AcceleratorTests].[testHelloWorld]
AS 
    BEGIN
 
 /*
  Return Hello World!
 */
    DECLARE @ret VARCHAR(15)
    EXEC getHelloWorld @pass = 1, @HELLO = @RET OUTPUT

 /*
  Check Hello World is returned
 */
    EXEC tSQLt.AssertEqualsString 'Hello World!', @ret

    END ;
GO

You will end up with a test in the SQL Test window if you choose refresh

Running your tests

Choose 'Run Tests' from the SQL Test window and there will be a tick or cross as to whether your test passed.

Fail Example

To see a failed test example alter the getHelloWorld stored procedure to pass in a 0 for the @pass parameter
eg. EXEC getHelloWorld @pass = 0, @HELLO = @RET OUTPUT

This is what the SQL Test window shows

And output in the SQL Test Messages

[AcceleratorTests].[testHelloWorld] failed: Expected:  but was: 

Related stuff

http://www.red-gate.com/products/sql-development/sql-test/
http://tsqlt.org/