ASP.NET VERSIONS: ALL
A Unit Testing Framework for SQL Server Stored Procedures
By Ken McNamee
If you ve been paying attention to this column over the past couple of years you should already be familiar with the concept of unit testing. First there was NUnit, which tests .NET assembly methods. And recently, I introduced you to NUnitASP, which extends NUnit for the purpose of testing ASP.NET user interfaces (see the March 2005 issue). This month, I m bringing you 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 t normally incorporate a structured framework. Most developers don t even directly test their stored procedures, but instead rely on their client application code to make the calls to the database, return values, report errors, and, hopefully, not hide any difficult-to-detect bugs. Closer to the metal, some developers design and build their stored procedures in a tool like SQL 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-increasing reliance on structured, automated testing frameworks such as NUnit. The main purpose of TSQLUnit is to bring this paradigm to stored procedure design, development, and testing. If you aren t familiar with unit testing, one of the most important things to note is that it is not simply something that gets done at the end of the development phase. Effective unit tests consist of code that you create before you even begin to write your business logic. Discovering what constitutes a successful test and what constitutes a failed test forces the developer to think more carefully during the design phase. This style of development almost always results in fewer bugs and fewer late nights for you, the developer.
Technically, TSQLUnit is not a tool. It doesn t have a user interface unless you count the tool you use to run queries against your database, such as SQL Query Analyzer. TSQLUnit is merely a small collection of tables and stored procedures that provide an API for testing your database code in an automated and structured manner. You use TSQLUnit by first installing it in your database, which only requires that you execute one SQL script.
The next step in using TSQLUnit is to write a unit test stored procedure. There are two things to note here: the name of the procedure and the syntax for notifying TSQLUnit when an error condition occurs. The name of the procedure is easy; simply prefix the name with ut_ and the rest is up to you. However, even though SQL Server has no concept of grouping unit tests into an assembly as NUnit does, there is still a way you can provide TSQLUnit some hints as to which unit tests belong together simply use an additional common name in the prefix, such as ut_MyTestSuite . So, for example, you may end up with a couple of unit tests named ut_MyTestSuite_TestCreateOrder and ut_MyTestSuite_TestCreateCustomer .
Figure 1 demonstrates using TSQLUnit to test a simple insert stored procedure for the Northwind database; Figure 2 shows the results displayed in SQL Query Analyzer. Basically, the goal is to execute the target stored procedure using some sample data and report an error if certain conditions arise (conditions that you define). In this example, the insert into the Orders table should have resulted in @@ROWCOUNT being equal to 1. However, because the CustomerID that was provided does not exist in the Customers table, a foreign key conflict occurred. This resulted in a call to the tsu_failure TSQLUnit procedure, which records the results in a table for reporting purposes.
--Stored procedure to test
CREATE PROC CreateOrderWithCustomerID
Orders (CustomerID, EmployeeID, OrderDate)
VALUES (@CustomerID, @EmployeeID, GetDate())
--Unit test stored procedure
CREATE PROCEDURE ut_TestCreateOrderWithCustomerID AS
EXEC CreateOrderWithCustomerID 'TEST1', 1
IF @@ROWCOUNT = 0
EXEC tsu_failure 'CreateOrderWithCustomerID failed'
Figure 1: This sample unit test, ut_TestCreateOrderWithCustomerID, demonstrates TSQLUnit s structured framework that gives developers the power to automate their stored procedure testing.
Figure 2: The tsu_RunTests procedure is a TSQLUnit command that runs all unit tests you define and reports the results back to the console or the SQL Query Analyzer messages window.
As you can see in Figure 2, the TSQLUnit command tsu_RunTests is all that was required to execute the unit test. In fact, this command will execute every unit test stored procedure prefixed by ut_ in the database. I mentioned earlier that you can group unit tests together into a test suite and TSQLUnit allows you to execute only this test suite by passing the name used in the prefix into tsu_RunTests as the first parameter. It may not be as elegant a solution as NUnit assemblies with [Test] attributes, but hey, it works. SQL Server even has a built-in method (SQL Jobs) for running your unit tests on a schedule, and a built-in storage mechanism (tables) for keeping track of what code worked and what didn t.
Given the limitations of SQL Server as a platform for hosting code, I think TSQLUnit is pretty darn ingenious. Like NUnit, TSQLUnit can 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. In highly specialized teams in which a DBA or dedicated database programmer creates all the stored procedures, a tool like TSQLUnit can be invaluable to the success of the project. Why should a T-SQL programmer need to learn C# or VB.NET to unit test their T-SQL code? They shouldn t, and that is just one place where TSQLUnit can be extremely useful.
Ken McNamee is a Senior Software Developer with Vertigo Software, Inc., a leading provider of software development and consulting services on the Microsoft platform. Prior to this, he led a team of developers in re-architecting the Home Shopping Network s e-commerce site, http://www.HSN.com, to 100% ASP.NET with C#. Readers can contact him at [email protected].