If a significant part of the business logic of your application is located in the database, you probably have been thinking about the unit testing of stored procedures and functions. Let's skip the discussion of whether it’s good or bad to put logic into stored procedures and see how to test the stored code. In this article, I will talk about tSqlt , a wonderful free open-source unit test framework for Sql Server.

Installation

tSqlt is distributed free under the Apache 2.0 open source license . The distribution kit in the form of an archive can be downloaded from the official site .

Before starting the installation of the framework, you need to configure an instance of Sql server to work with the CLR :

EXEC sp_configure 'clr enabled', 1; RECONFIGURE; 

And declare the target database as trusted ( property TRUSTWORTHY ).

 DECLARE @cmd NVARCHAR(MAX); SET @cmd = 'ALTER DATABASE ' + QUOTENAME(DB_NAME()) + ' SET TRUSTWORTHY ON;'; EXEC(@cmd); 

In the archive you will find a sql script that must be executed in the target database. The script will create its own tSqlt scheme, CLR assembly and many procedures and functions. Some procedures will contain the prefix Private_ and are intended for internal use by the framework itself.

Work with tests

A test is a stored procedure whose name begins with the word “test”. For convenience, tests are combined into “classes”, which are Sql Server schemas. Each class can have its own SetUp procedure, which will be called before each test run.
You can create a new class by using the NewTestClass procedure .

 EXEC tSQLt.NewTestClass 'MyTestClass' 

You can run tests all at once, by class and one by one. To do this, use the Run and RunAll procedures :

 --    EXEC tSQLt.RunAll; --     MyTestClass EXEC tSQLt.Run 'MyTestClass'; --   FisrtTest  MyTestClass EXEC tSQLt.Run 'MyTestClass.FisrtTest'; --    . --    FisrtTest   MyTestClass EXEC tSQLt.Run; 

Opportunities

If you have ever used any framework for unit tests, you will be pleasantly surprised if you don’t find any serious differences in tSqlt .
A remarkable feature of tSqlt is the isolation of tests from each other, implemented using the transaction mechanism.
In addition, tSqlt contains a number of useful procedures for test output, which help determine what went wrong in the test.
')
A typical test consists of three parts:

1. Preparing the environment / test data
2. Execution of the code under test
3. Checking results

I'll tell you about them in order:

Preparing the environment / test data

At this stage, we need to prepare the database objects that will be used by the code under test - replace them with plugs, Stub and Mock objects.

What can be replaced:

Object type	Procedure	Result
Table	Faketable	Stub
Procedure	Spyprocedure	Mock
Function	Fakefunction	Stub

To substitute tables, the framework provides a FakeTable procedure that creates a copy of the target table without data.

 tSQLt.FakeTable [@TableName = ] '  ' , [[@SchemaName = ] ' '] , [[@Identity = ] ' '] , [[@ComputedColumns = ] '  ] , [[@Defaults = ] '   '] 

By default, calculated fields, default values, and identity columns will not be preserved; however, this can be changed with the optional parameters @identity , @ComputedColumns, and @Defaults .
The function can NOT replace temporary tables, objects of other databases, and also does not save foreign keys. The FakeTable procedure will create a stub (Stub) that you can fill with test data without having to change the real object. This will give you the opportunity to run tests independently from each other to several users simultaneously on one instance of Sql Server.

The FakeFunction procedure replaces the actual function with a stub.

 tSQLt.FakeFunction [@FunctionName = ] '  ' , [@FakeFunctionName = ] ' ' 

The SpyProcedure procedure creates a Mock object, replacing the actual procedure and saving the parameter values ​​with which the procedure will be invoked. tSqlt create a special table with parameters for calling a substituted procedure, adding the postfix "_SpyProcedureLog" to the procedure name. If your procedure was called, for example, CalcSales , then its parameters will be saved in the CalcSales_SpyProcedureLog table.
If, in addition to storing arguments, you need the Mock object to perform some operation or return a value, you can pass the Sql script in the @CommandToExecute parameter.

 tSQLt.SpyProcedure [@ProcedureName = ] ' ' [, [@CommandToExecute = ] ' ' ] 

Execution of the code under test

The easiest part is here you run the code you want to test.
It is worth mentioning that if you expect the code under test to throw an exception, you need to notify tSqlt in advance by calling ExpectException

 tSQLt.ExpectException [ [@ExpectedMessage= ] '  '] [, [@ExpectedSeverity= ] '  '] [, [@ExpectedState= ] '  '] [, [@Message= ] '  '] [, [@ExpectedMessagePattern= ] '  '] [, [@ExpectedErrorNumber= ] '  '] 

Attached to this procedure is the ExpectNoException procedure, which checks that an exception was not thrown.

 tSQLt.ExpectNoException [ [@Message= ] '  '] 

Checking results

To compare the results of the test code, with our expectations, we use a set of procedures, expectedly named Assert *. Naturally, you can use your own code to compare results and expectations by calling the tSQLt.Fail procedure with a description of the error if the test fails. However, using Assert * procedures makes the test more readable and similar to the usual unit tests. In addition, adding logic to the test (even if elementary) is not a good idea.

Assert * procedures provided by the framework:

Procedure	Description
Assertnotquals	Checks that the two values ​​are NOT equal. WARNING: NULL at the expected value will result in an error -- EXEC tSQLt.AssertEquals NULL, NULL;
AssertEmptyTable	Verifies that the procedure is empty
Assertequals	Checks that the two values ​​are equal. ATTENTION: In this case, NULL is NULL -- EXEC tSQLt.AssertEquals NULL, NULL;
AssertEqualsString	Checks that two strings are equal. ATTENTION: In this case, NULL is NULL -- EXEC tSQLt.AssertEqualsString NULL, NULL;
AssertObjectExists	Checks the existence of an object.
Fail	Finishes the test with the given error
AssertObjectDoesNotExist	Checks that the object does NOT exist.
Assertlike	Checks that between expected and actual value is the operator LIKE ATTENTION: In this case, NULL is NULL -- EXEC tSQLt.AssertLike NULL, NULL;

I will describe one more special procedure AssertEqualsTable separately.
This procedure compares the contents of two tables. To pass the test successfully, the resulting table must have the same columns and the same values ​​in them as the table with the expected values. However, if these two tables, according to AssertEqualsTable , are absolutely equal:

  CREATE TABLE expected( A INT ) CREATE TABLE actual( A BIGINT, B INT ) --    EXEC tSQLt.AssertEqualsTable 'expected', 'actual'; 

If you need a tighter comparison of table metadata, additionally use the procedure AssertResultSetsHaveSameMetaData

  CREATE TABLE expected( A INT ) CREATE TABLE actual( A BIGINT, B INT ) --    EXEC tSQLt.AssertEqualsTable 'expected', 'actual'; --      EXEC tSQLt.AssertResultSetsHaveSameMetaData 'SELECT * FROM expected', 'SELECT * FROM actual'; 

ATTENTION: If the tables contain text type, ntext, image, xml, geography, geometry, rowversion type fields or any CLR types not marked as “comparable” or “byte ordered”, an exception will be thrown.

Example

Consider a simple example: the CalcAvgTemperature procedure calculates the average temperature over a range of dates based on the data in the temperature table.
The PrintAvgTemperatureLastFourDays procedure uses the CalcAvgTemperature procedure to calculate the average temperature over the last four days.

Create a new test class TemperatureTests

 EXEC tSQLt.NewTestClass 'TemperatureTests' 

Add one test for each of our procedures.

To run both tests, you can use the Run procedure and pass it the name of our test class TemperatureTests.

 EXEC tSqlt.Run 'TemperatureTests' 

As expected, the tests were successful and in the output we will see:

 +----------------------+ |Test Execution Summary| +----------------------+ |No|Test Case Name |Result | +--+----------------------------------------------------------+-------+ |1 |[TemperatureTests].[Test_CalcAvgTemperature] |Success| |2 |[TemperatureTests].[Test_PrintAvgTemperatureLastFourDays]|Success| ----------------------------------------------------------------------------- Test Case Summary: 2 test case(s) executed, 2 succeeded, 0 failed, 0 errored. ----------------------------------------------------------------------------- 

Special features

Do not forget that each run of the tSQLt test wraps into a transaction. Therefore, if you use your own transactions in your stored procedure, this should be done carefully. For example, the test of such a procedure will end with an error:

Although outside the test procedure will work without error. The cause of the problem is that the ROLLBACK in the procedure will roll back not only your transaction, but also the transaction tSqlt and the number of active transactions will change at the exit of the procedure. This problem is described here , and its solution can be found here .

For dessert

For those who like a graphical interface, green and red checkmarks opposite tests and the like, Redgate has developed SQL Test - a very powerful plugin for Sql Managment Studio, based on tSqlt and allowing you to do all the work with tests from the menu.