Unit Testing TSQL in SQL Server 2005and the tSQLT Framework–part 1

 

Installing tSQLT

Installing tSQLT is pretty simple. You can find the .zip file containing two SQL script files here http://sourceforge.net/projects/tsqlt/ . After downloading the zip and extracting it, run the two .sql files on your desired database in this order: SetClrEnabled.sql, tSQLt.class.sql. That’s it, tSQLT has been installed.

There is also a tutorial here: http://www2.sqlity.net/articles/tSQLtTutorial and an user guide here: http://www2.sqlity.net/tsqlt/tsqlt-user-guide 

After running tSQLt.class.sql, you may get the following warnings:

Compiled at 2011-02-27 12:57:27.803
The module 'DefaultResultFormatter' depends on the missing object 'tSQLt.TableToText'. The module will still be created; however, it cannot run successfully until the object exists.
The module 'RunTestClass' depends on the missing object 'tSQLt.Run'. The module will still be created; however, it cannot run successfully until the object exists.
The module 'Run' depends on the missing object 'tSQLt.private_RunTestClass'. The module will still be created; however, it cannot run successfully until the object exists.
The module 'SetFakeViewOff' depends on the missing object 'tSQLt.private_SetFakeViewOff_SingleView'. The module will still be created; however, it cannot run successfully until the object exists.

You can ignore them for now and take a look at your database, and under  stored procedures you should see a whole bunch using the tsql schema:

tSQLt created those, along with a view, some tables, table-valued functions, scalar-valued functions and other objects, including a CLR assembly.

Testing with tSQLt

Let’s start with a simple test. Testing a scalar-valued function that returns the last working day of previous month.

Here is the function we want to test:

CREATE FUNCTION [dbo].[fnGetLastWorkingDayOfPreviousMonth] 
(
    -- Add the parameters for the function here
    
)
RETURNS datetime
AS
BEGIN
    declare @lastdayofpreviousmonth datetime
declare @lastworkingdayofpreviousmonth datetime
if(@@DATEFIRST=7)
begin
    select @lastdayofpreviousmonth =  DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0))
    if(DATEPART(dw, @lastdayofpreviousmonth) between 2 and 6)
    set @lastworkingdayofpreviousmonth= @lastdayofpreviousmonth
    else
    set @lastworkingdayofpreviousmonth = DATEADD(day, -(1+DATEPART(dw, @lastdayofpreviousmonth)%@@DATEFIRST), @lastdayofpreviousmonth) 
end
return @lastworkingdayofpreviousmonth

END

First thing to do is set up a test class. This is essentially a schema we assign to all our tests. Using this class, we can run all tests inside it, or choose to run them selectively.

EXEC tSQLt.NewTestClass 'DemoTSQLT';
GO

Creating a test for our function is as easy as creating a stored procedure having the schema 'DemoTSQLT'.  tSQLt provides a nice way to test if two values are equal : tSQLt.assertEquals. The test for our function would look like this:

Create procedure [DemoTSQLT].[test last working day in previous month]
AS
BEGIN
declare @expectedResult datetime
set @expectedResult = cast('01-31-2011' as datetime)
declare @actualResult datetime
select @actualResult = dbo.fnGetLastWorkingDayOfPreviousMonth() 

exec tSQLt.assertEquals @expectedResult, @actualResult
END
GO

Because the function to test was designed to include the time portion of the date, effectively making ‘2011-01-31 23:59:59.000’ the last working day of previous month, this test will fail spectacularly. To run the test and see it fail, we can simply do the following:

tsqlt.Run 'DemoTSQLT'
GO

The output of this test can be seen bellow:

Here, we ran all tests in  ‘DemoTSQLT ‘ class. To run a specific test, change tsqlt.Run to tsqlt.RunTest like this:

tsqlt.RunTest '[DemoTSQLT].[test last working day in previous month]'
GO

Let’s change the test a bit: Where we cast ‘01-31-2011’  as datetime, let’s change that to ‘2011-01-31 23:59:59.000'. Running the test (tsqlt.Run ‘DemoTSQLT’) now will have this result:

+---------------------+
|Test Execution Sumary|
+---------------------+
 
|No|Test Case Name                                       |Result |
+--+-----------------------------------------------------+-------+
|1 |[DemoTSQLT].[test last working day in previous month]|Success|
-----------------------------------------------------------------------------
Test Case Summary: 1 test case(s) executed, 1 succeeded, 0 failed, 0 errored.
-----------------------------------------------------------------------------

Test succeeded, developer happy, function is ready to be used.

There you have it. There is a lot more to tSQLt to be sure and I will try to cover it as much as possible in the next parts of the series. Have fun and happy testing.

Cheers,
Radu