TSQLUnit

ToolKit

LANGUAGES:T-SQL

ASP.NETVERSIONS: ALL

TSQLUnit

A Unit Testing Framework for SQL Server StoredProcedures

By Ken McNamee

If you ve been paying attention to this column over thepast couple of years you should already be familiar with the concept of unittesting. First there was NUnit, which tests .NET assembly methods. And recently,I introduced you to NUnitASP, which extends NUnit for the purpose of testingASP.NET user interfaces (see the March 2005 issue). This month, I m bringingyou TSQLUnit (http://tsqlunit.sourceforge.net),which is a framework for unit testing T-SQL stored procedures using ... well,more T-SQL stored procedures.

Unit Testing Methodology

Traditional methods for testing stored procedures don tnormally incorporate a structured framework. Most developers don t evendirectly test their stored procedures, but instead rely on their clientapplication code to make the calls to the database, return values, reporterrors, and, hopefully, not hide any difficult-to-detect bugs. Closer to themetal, some developers design and build their stored procedures in a tool likeSQL Query Analyzer, create ad hoc scripts to run the procedures, and, finally,analyze the results manually.

Over in the .NET world, code is being improved by an ever-increasingreliance on structured, automated testing frameworks such as NUnit. The mainpurpose of TSQLUnit is to bring this paradigm to stored procedure design,development, and testing. If you aren t familiar with unit testing, one of themost important things to note is that it is not simply something that gets doneat the end of the development phase. Effective unit tests consist of code thatyou create before you even begin to write your business logic. Discovering whatconstitutes a successful test and what constitutes a failed test forces thedeveloper to think more carefully during the design phase. This style ofdevelopment almost always results in fewer bugs and fewer late nights foryou, the developer.

Using TSQLUnit

Technically, TSQLUnit is not a tool. It doesn t have auser interface unless you count the tool you use to run queries against yourdatabase, such as SQL Query Analyzer. TSQLUnit is merely a small collection oftables and stored procedures that provide an API for testing your database codein an automated and structured manner. You use TSQLUnit by first installing itin your database, which only requires that you execute one SQL script.

The next step in using TSQLUnit is to write a unit teststored procedure. There are two things to note here: the name of the procedureand the syntax for notifying TSQLUnit when an error condition occurs. The nameof the procedure is easy; simply prefix the name with ut_ and the rest is upto you. However, even though SQL Server has no concept of grouping unit testsinto an assembly as NUnit does, there is still a way you can provide TSQLUnitsome hints as to which unit tests belong together simply use an additionalcommon name in the prefix, such as ut_MyTestSuite . So, for example, you mayend up with a couple of unit tests named ut_MyTestSuite_TestCreateOrder and ut_MyTestSuite_TestCreateCustomer .

Figure 1 demonstrates using TSQLUnit to test a simpleinsert stored procedure for the Northwind database; Figure 2 shows the resultsdisplayed in SQL Query Analyzer. Basically, the goal is to execute the targetstored procedure using some sample data and report an error if certainconditions arise (conditions that you define). In this example, the insert intothe Orders table should have resulted in @@ROWCOUNT being equal to 1. However, becausethe CustomerID that was provided does not exist in the Customers table, aforeign key conflict occurred. This resulted in a call to the tsu_failure TSQLUnit procedure, which records the results in a table for reportingpurposes.

 --Stored procedure to test

 CREATE PROCCreateOrderWithCustomerID

  @CustomerID nchar(5),

  @EmployeeID int

 AS

 INSERT INTO

  Orders (CustomerID,EmployeeID, OrderDate)

  VALUES (@CustomerID,@EmployeeID, GetDate())

 --Unit test storedprocedure

 CREATE PROCEDUREut_TestCreateOrderWithCustomerID AS

 EXECCreateOrderWithCustomerID 'TEST1', 1

 IF @@ROWCOUNT = 0

  EXEC tsu_failure'CreateOrderWithCustomerID failed'

Figure 1: Thissample unit test, ut_TestCreateOrderWithCustomerID, demonstrates TSQLUnit sstructured framework that gives developers the power to automate their storedprocedure testing.

Figure 2: The tsu_RunTests procedureis a TSQLUnit command that runs all unit tests you define and reports theresults back to the console or the SQL Query Analyzer messages window.

As you can see in Figure 2, the TSQLUnit commandtsu_RunTests is all that was required to execute the unit test. In fact, thiscommand will execute every unit test stored procedure prefixed by ut_ in thedatabase. I mentioned earlier that you can group unit tests together into atest suite and TSQLUnit allows you to execute only this test suite by passingthe name used in the prefix into tsu_RunTests as the first parameter. It maynot be as elegant a solution as NUnit assemblies with [Test] attributes, buthey, it works. SQL Server even has a built-in method (SQL Jobs) for runningyour unit tests on a schedule, and a built-in storage mechanism (tables) forkeeping track of what code worked and what didn t.

Conclusion

Given the limitations of SQL Server as a platform forhosting code, I think TSQLUnit is pretty darn ingenious. Like NUnit, TSQLUnitcan even be provided with Setup and Teardown procedures in your test suite.This makes it just as powerful as NUnit for testing your database logic. Inhighly specialized teams in which a DBA or dedicated database programmercreates all the stored procedures, a tool like TSQLUnit can be invaluable tothe success of the project. Why should a T-SQL programmer need to learn C# orVB.NET to unit test their T-SQL code? They shouldn t, and that is just oneplace where TSQLUnit can be extremely useful.

Ken McNamee is aSenior Software Developer with Vertigo Software, Inc., a leading provider ofsoftware development and consulting services on the Microsoft platform. Priorto this, he led a team of developers in re-architecting the Home ShoppingNetwork s e-commerce site, http://www.HSN.com,to 100% ASP.NET with C#. Readers can contact him at [email protected].