Unit Test for T-SQL(ZT)

Testing, 1, 2, 3... Testing... Check?

S. A. Miller
If you're like me, you've probably spent a lot of time developing your code in Query Analyzer. Once you're happy with the code, you run an ad hoc test or two on a test database on a development server. If that looks okay, you put the code into production. If it's a critical piece of code, or if it's more complicated, you might do a few more checks to shield posterior anatomy. And even then, you might hold your breath.

This is the way I coded for most of my career. Oh, sometimes I stored off my test queries for future use, usually because the President/CEO/CIO/Department Manager was in the habit of changing his or her mind about what he or she wanted every week or so. But I didn't do much more than that. I typically tested with ad hoc queries out of Query Analyzer, or its Oracle/Access/FoxPro equivalent. More intense tests required the Query Analyzer debugger. Desperation required PRINT statements.

There is a better way.

Beyond ad hoc tests

When my department at SIL adopted Extreme Programming (XP), we also adopted the unit tests portion of the methodology, and they've both made me a better developer. But even if you don't work in an XP environment, you can still benefit from XP-styled unit tests.

Unit tests are different from acceptance tests. Unit tests test a small block of code, such as a stored procedure, while acceptance tests have more to do with the UI being acceptable to a user. Here are five benefits of unit testing that I've discovered:

·                     They tend to finger the guilty party. Have you ever received an e-mail telling you to fix your bug when it's really a side effect of someone else's changes? Well, if you have some test queries lying around, wrap them into stored procs, which can be run regularly, perhaps evenings. Make sure an e-mail is generated when a unit test fails.

·                     ;It won't take you long to build a library. Each stored procedure and each stored function should have tests written for it. And so should triggers. If this sounds grueling, think of how good it will feel to save yourself by catching a problem before it hits the production server. If you have large quantities of legacy code, writing a test for every unit might take years of work, and you can't stop new development just to write tests. But you can write tests for each new piece of code, and also for each procedure you modify. It won't be long before you have a battery of tests for both critical legacy code and new code.

·                     Create accurate code documentation with ease. Each procedure or function should be called with different combinations of parameters. Not only does this ensure your code works as intended, it also provides current, accurate documentation of your work. Another coder simply has to look at your test to see an example of calls to your procedure. Who knows? That other coder might just be you someday.

·                     They force you to do a bit of up-front thinking and planning. You should write your unit test before you write the actual procedure or function. "What?" you say. "I protest! How can we write a test for something we haven't even coded yet?"

·                     There's an old joke that shows a manager saying, "I'll go find out what they want. The rest of you start coding." Well, the coders can't start work until they know what they're supposed to code, can they? When you write a test first, you're forced to think about what you want your procedure to do before you start coding the procedure, not later.

·                     They really do save you time. Developers have been known to complain that coding the test takes more time than writing the actual procedure. And sometimes it does. But consider this: I recently had the task of modifying one of the most difficult stored procedures I'd ever run across. It was legacy code, but I still wrote the test first. It took several days to finish, due in part to the demands placed on the procedure. The test proved to be important for all the reasons I've just listed, but it became invaluable when I had to rewrite the procedure for performance.

·                     The unit tests showed numerous errors in the rewrite, and I was able to find the cause of each in a fraction of the time it would have taken me without unit tests. Then, when I thought I was finished, an obscure test failed. One of the variables in the main loop had a flaw in it. Had the code been released to production in that state, it would have been an elusive bug to track. In the end, I finished faster than I could have otherwise.

How to write T-SQL unit tests

Before I tell you about a testing framework for T-SQL, here are reminders of two very basic principles:

·                     First, you need a database with good test data. By "good data," I mean live data from the real world. No matter how good a programmer you are, you'll never be able to mock up data adequately for an application. Even if the legacy system you're replacing is made of paper, get a data entry person to enter data into some tables. Do what it takes to get real data. [There are test data generators, though. See my tip, "Generating Test Data," in this issue.–Ed.]

·                     Second, you shouldn't be developing against a production database. You should have a development or test database that you can explode to your heart's content. Back when I was developing in Oracle, I once had to fight for a week to put a development database on an aging server. SQL Server developers don't have that excuse.

Once you have your development database up with good data, you need some sort of framework in which to run your tests. You could write your own, but why should you? There's one already available to you.

Introducing TSQLUnit

TSQLUnit is an open source unit testing framework for T-SQL written by Henrik Ekelund and available from http://sourceforge.net/projects/tsqlunit. Here's an example of how I've used it.

My TSQLUnit tests take a similar pattern of three parts: 1) unit test setup, 2) execution of the target procedure, and 3) checking results.

In the unit test setup, I often check to make sure someone hasn't done bad things to my data when I wasn't looking:

DECLARE @nId INT, @nNewId INT —- @nNewId is for later
SELECT @nId = [ID]
FROM MyTable
WHERE MyField = 'whatever'
IF @nId IS NULL -- or @@ROWCOUNT = 0
    EXEC tsu_failure 'The data has changed. ''whatever'' couldn''t be found'

The IF block checks for the expected record. If it couldn't be found, the test fails and will generate an error message. The test framework moves on to the next unit test. You don't need to use the name of the unit test in the failure message string, because TSQLUnit will name it for you when the test fails.

Now I call the stored procedure I'm about to write:

EXEC CreateMyTableNewRec @nId, @nNewId OUTPUT

As you can see, I've determined that I need an output parameter from this new procedure. In checking the results, I make sure the output parameter really is filled with something:

IF @nNewId IS NULL
    EXEC tsu_failure 'A new record was not created for table MyTable.'

I could further check the value to see if the new record was created in the way I wanted it to be created.

Each TSQLUnit test is itself a stored procedure. Listing 1 shows what one looks like when all of the pieces are put together:

Listing 1. A complete unit test for T-SQL.

CREATE PROCEDURE ut_MyTable_NewRec
AS
--== Setup ==--
DECLARE @nID INT, @nNewId INT
SELECT @nId = ID FROM MyTable
WHERE MyField = 'whatever'
IF @nId IS NULL -- or @@ROWCOUNT = 0
    EXEC tsu_failure 'The data has changed. ''Whatever'' couldn''t be found'
--== Execute ==--
EXEC CreateMyTableNewRec @nId, @nNewId OUTPUT
--== Check ==--
IF @nNewId IS NULL EXEC tsu_failure 'A new record was not created for table MyTable.'
GO

Note the three-part name of the stored procedure, ut_MyTable_NewRec. The prefix "ut_" alerts TSQLUnit that this is a unit test it should run. If you already use this prefix ut_ for other purposes, TSQLUnit lets you set it to something else. "MyTable" is the name of a group of related unit tests, known as a suite of tests. For instance, you could add another unit test called ut_MyTable_DeleteRec. The MyTable suite would test both adding and deleting a record to MyTable. The suite can be run separately from other test suites. The third part of the name–"NewRec" or "DeleteRec"–uniquely identifies this unit test.

Note that you don't need BEGIN TRAN and ROLLBACK in each unit test; TSQLUnit takes care of this for you.

Running the unit test

In order to run the unit test in Listing 1, you need to set up the framework. From Query Analyzer, run tsqlunit.sql on your development database. You need do this only once for the database. Next, create procedure ut_MyTable_NewRec, if you haven't already. Now you're set. Simply execute the unit test:

-- This will run all tests for suite MyTable, EXEC tsu_RunTests MyTable

Fixtures

Suppose I want numerous records to be available for all the unit tests of a suite. I don't want to write the same setup code for each test. TSQLUnit solves the problem with a setup fixture. The code in the fixture will be run before each unit test.

For instance, the setup fixture for the previous MyTable suite would be named ut_MyTable_setup. The third part of the name "setup" alerts TSQLUnit to treat the procedure as a setup fixture for the suite. It will look something like this:

CREATE PROCEDURE ut_MyTable_setup
AS
    INSERT INTO MyTable ([Description])
    VALUES ('something') --( more records inserted here 
GO

The SQL Server community owes a huge debt of gratitude to Henrik Ekelund and his employer for making TSQLUnit open source.

Link to

http://sourceforge.net/projects/tsqlunit

Link to

http://tsqlunit.sourceforge.net/tsqlunit_cookbook.htm (documentation)