CoderJony

Database Unit Testing for SQL Server using tSQLt

In this blog, I am sharing the key points that I noted while learning the basics of database unit testing for SQL Server.

Installation:

  • tSQLt must be installed on the SQL Server database.
  • Download the .zip file from its website.
  • Unzip that file, there will be mainly 3 SQL files.
    • SetClrEnabled.sql
    • tSQLt.class.sql
    • Example.sql
  • First Enable CLR from the first file.
  • Second, set the database trustworthy from the script given in the link
  • Third, run tSQLt.class.sql
  • These 3 steps will install tSQLt on your database.

Creating a Test Class

  • A Test Class is basically a database schema with some special properties applied so that tSQLt can recognize them as a test class.
  • A Test Class can contain many Test Cases. (Note: A Test Case is nothing but a Stored Procedure.)
  • To create a Test Class, we must run the below command.
EXEC tSQLt.NewTestClass 'MyTestClass'; 
GO

Creating a Test Case

  • 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".

Running Test Cases

  • RunAll - RunAll executes all test cases on all test classes.
    • i.e EXEC tSQLt.RunAll;
  • Run - Run is a versatile procedure for executing test cases. It can be called in three ways:
    • With a test class name
    • With a qualified test case name (i.e. the schema name and the test case name)
    • With no parameter
  • Creating a Test Class, Test Case & Running them all

Important Points:

Buy Me A Coffee