TSQLUnit

Here is a short cookbook showing you the steps you can follow in writing and organizing your own tests using TSQLUnit.

Why?

I guess that most T-SQL developers use a combination of PRINT statements and ad-hoc tests with the SQL Query Analyzer when developing stored procedures. Some use the T-SQL debugger to inspect the values of variables. In all cases, human judgment is required to analyze the results. They are also ad-hoc, the tests can not easily be repeated again. Tests done with TSQLUnit or any of the other xUnit framework however do not have these disadvantages. I believe that there are several advantages of using automatized tests.

1. They can be repeated over and over again. Many good automatic tests may give you the courage to improve the quality of your code without being afraid of breaking anything. Improving the quality of the code is often called refactoring.
2. The program code for the tests also serve the purpose of documentation. They explain what is expected to happen in the stored procedures you write.
3. Writing the tests before you write the stored procedure makes you think carefully about its interface, and may improve the design. 

Automatized tests are one of the cornerstones of a methodology called extreme programming, which tries to solve many of the traditional problems with software development. You can read more about it at http://www.xprogramming.org/ or http://www.extremeprogramming.org/.

How?

When you need to test something, here is what you do:

1. Create a stored procedure with a name that starts with ut and underscore, such as ut_testSomething
2. Code a test, then call tsu_failure if the test fails
3. Execute tsu_runTests, it runs the test you have made and shows the result.

Here is an example. You want to test that the stored procedure capitalize formats a string so that the first word in a sentence starts with an uppercase letter.

CREATE PROCEDURE ut_testCapitalize AS
BEGIN
DECLARE @outStr VARCHAR(500)
EXEC capitalize 'a string', @outStr OUT
IF (ASCII(LEFT(@outStr,1)) <> ASCII('A')) OR @outStr IS NULL
EXEC tsu_failure 'Capitalize should make the first character uppercase'
END

In addition to this test I would also recommend that you make more tests of the capitalize  procedure. You could test what happens when the input is NULL, that the procedure works if there are more than one sentence in the string and so on. When you make many similar tests they often use the same code to populate tables and other input data. In these cases you could create a Fixture.

TestSuites 

The stored procedure tsu_runTests runs all your tests by default. When the number of tests grows this may take quite a while. You may also want to group similar tests together so it is more obvious which tests belong together. In the object oriented xUnit frameworks this is accomplished by keeping the tests in the same TestSuite class. In TSQLUnit you can group tests together by adding a common prefix to the name of your tests. 

Suppose you have three tests, ut_CapitalizeOneSentence, ut_CapitalizeWithNull and ut_testCalcSalesForAllCustomers. Since the first two are related you want to make a separate test suite for them which you can run separately from the long running third test. Just rename them ut_capitalizeTests_oneSentence and ut_capitalizeTests_withNull and there you have it, a test suite called "capitalizeTests". If you want to run both test in this suite, execute tsu_runTests 'capitalizeTests'.

Fixtures

Many tests needs to have quite a lot of prepared data as a background. The background is called a test fixture. The fixture can often be shared between similar testcases. This can make the testcode shorter and clearer. In TSQLUnit you can add a fixture to a test suite by creating a special procedure called "setup". The setup procedure will be executed before each test in the testsuite. The setup procedure and the test procedure are executed in a transaction that is rolled back, so you need not be concerned about the order in which the test execute. There is no risk that a previously executed test changes the fixture. 

To add a fixture to the "CapitalizeTests" test suite, create a stored procedure called ut_capitalizeTests_setup. 

If you have the need to explicitly clean up something that is not automatically rolled back, you can make a stored procedure called "teardown". In this example i should be named ut_capitalizeTests_teardown. An example of where a teardown procedure can be useful is if you create a file in the setup procedure. It will not be removed because the transaction is rolled back, so you will have to delete it in the teardown procedure.

Here is a longer example of how a setup procedure may used. We want to test that the procedure SalesByCategory in the Northwind database calculates the sales figures correctly both with and without a discount. Two testcases are needed for this. The query in the SalesByCategory procedure uses data from four tables, and we can use a fixture to populate them with the data that is needed as a common background for our testcases. Pseudocode for the procedures are below, complete code can be found here.

CREATE PROCEDURE ut_salesbycategory_setup AS
BEGIN
-- 1. Remove foreign key constraints related to for the tables Categories, Products and Orders.
--    This makes it easier to clear the tables and insert testdata.
-- 2. Delete all existing data for the tables Categories, Products and Orders.
-- 3. Insert one row of test data for each table
END
CREATE PROCEDURE ut_salesbycategory_noDiscount AS
BEGIN
-- 1. Create a variable @ok that will hold the result of the test.
-- 2. Clear all data from the table [Order Details].
--    Insert a new row in [Order Details] with UnitPrice=100 and Quantity=10
-- 3. Execute SalesByCategory
-- 4. If the returned TotalPurchase is 1000 (UnitPrice*Quantity), set @ok to 1
-- 5. If @ok is zero, Execute tsu_failure
END
CREATE PROCEDURE ut_salesbycategory_discount AS
BEGIN
-- Do exactly as ut_salesbycategory_noDiscount, but add a discount of 30 percent  
-- to the row in [Order Details]. Test that SalesByCategory is 700 (70 percent of 1000).
END

Some suggestions

Test one feature only per test procedure.

Be careful of how the T-SQL comparision operators work when an expression is null. The pattern for the @ok variable in the procedure ut_salesbycategory_noDiscount above solves some problems with this, since it returns a failure in all cases except when the test is correct.

Refactor your testcases when the number of testcases grows. Make sure you understand what they do.

When it is difficult to write tests it can be a sign that you should change the design of the interface or refactor your system.

Do not run your tests in a production system. Even if TSQLUnit should rollback all changes made by the test procedures, you can never know for sure. Until we live in a world free of bugs, be careful of this. If you feel like being dangerous, you should make a backup before you run tests, or better run you tests in a copy of  the database.