Used to be, procedure testing was a last-minute sanity check before releasing a new procedure: "Does the procedure execute cleanly and complete its assigned work?" If you answered yes, you could release the procedure and move on. Few busy DBAs had time to worry about atypical usage scenarios such as illegal parameter values, missing data, or unavailable objects such as work tables. But this straightforward approach no longer works. We now know that stored procedures are just like other software: Before release, we must test and retest them until they meet established standards. And therein lies the rub—because unit testing isn't much fun. It requires laboriously entering test values, tracking down test results, trying to figure out whether the procedure did everything it was supposed to do—and nothing else.
When a task is no fun, the programmer in us naturally starts looking for ways to automate the boring stuff. Wouldn't it be great to cut the time it takes to unit test your T-SQL stored procedures? Well, now you can! You can automate your procedure testing by using nothing more than Query Analyzer and a little T-SQL code. The solution has four easy-to-implement parts that you can extend as needed: a table that stores your test cases and identifies the procedures you want to run; dynamically generated T-SQL for executing test cases against your procedures; a table that stores your test results; and SQL Server's sp_makewebtask system stored procedure for reporting your test results.
Unit Testing and Test Automation
To get started, let's quickly review what's involved in unit testing a stored procedure. At its simplest, testing exercises code for the purpose of finding errors. For nonparameterized stored procedures, "exercising the code" simply means submitting an execution request with the correct procedure name. For procedures that contain input parameters, it means including parameter values along with the procedure name; and for those that contain output parameters, it means declaring a local variable to receive the output values. Nothing complicated here, as far as the basic mechanics go.
What sometimes makes matters tricky is choosing the right test values and identifying how those values interact with the state of your database at the time you run your tests. I assume you already know what test values you want to use without delving into value-selection strategies such as equivalence partitioning (e.g., choosing input values that exercise valid and invalid resource conditions, such as inserting into an existing table—valid—versus trying to insert into a nonexistent table—invalid) and boundary-value analysis (e.g., choosing input values that probe extreme conditions, such as months 1 and 12 when testing dates). By the "state" of your database, I mean its preconditions (i.e., its requirements or dependencies), such as the objects a procedure requires and whether the user has permission to access those objects.
When you know the state of your database, you can figure out the setup and execution mechanics you must simulate to automate your procedure tests. Running a test against a stored procedure requires that you know several values: the values of any input parameters passed to the procedure, the procedure's return value (to help you decide whether any faults have occurred), any parameter values you expect the procedure to output, and any values the procedure prints or sends to a table. When you run a test, you compute a pass or fail result by comparing expected outputs with actual outputs and checking a procedure's return value for fault conditions (in the form of custom error codes) such as a missing table or improper permissions.
So, the basics of test mechanics are simple. Procedure tests can also check the pre-test and post-test states of database objects (e.g., the insertion of records into a table), but I won't go into those kinds of tests here.
Recording Your Test Cases
So how do you automate the mechanics of executing a test case? In practice, a test case includes a procedure name and any associated input values, output parameters, or return values. Let's start by creating a table to store the names and parameters of some sample procedures for later testing, as the code in Listing 1 shows. To keep it simple, I've included both procedure names and parameter data in the same table, tabTestValues. That way, all test data are centrally located, so you can easily add new test cases and query the table for quality assurance reports. (If you require normalization, you might want to create separate tables for your procedures and parameter data.) Table 1 shows a description of each column in tabTestValues. If you want, you can easily extend tabTestValues to include additional information such as the test-case author; date and time stamps; the procedure's version number (for source control and build-management purposes); the procedure owner (for notification purposes, particularly when a test fails); and the application requirement or feature the procedure implements (to ensure traceability).
Now that we've got a place to store our tests, let's populate tabTestValues with some sample test cases. The first three INSERT statements in Listing 2 create a test case for a stored procedure named Sproc_1, which occupies three rows in TabTestValues—one row for each of its input parameters. (Listing 3 shows the creation code for the stored procedures that the tabTestValues table references.) Sproc_1 has no output parameter and only prints its input values.
The next four INSERT statements in Listing 2 create a test case for Sproc_2, a procedure containing three input parameters and an output parameter—total_cost—that's a money data type. Sproc_2's logic is a bit more complex. After receiving a product name, quantity, and price, Sproc_2 calculates the total order cost (quantity * price) and then uses the output parameter, @total, to pass it to the calling process (Query Analyzer in this case). The next two INSERT statements in Listing 2 create a test case for Sproc_3. The procedure requests a last name, then uses an output parameter to return a varchar(30) error message. This logic shows how an automated test driver might handle different data types. Finally, the up_lookupPrice procedure queries a table called Products (which we omit on purpose for this example to force a "missing resource" error) for a price, then returns the price to the calling program. Up_lookupPrice introduces additional realism by adding realtime error checks and return codes commonly used in production-level stored procedures. The procedure also forces the test driver to deal with a common dependency violation in the missing Products table.
Prev. page
[1]
2
next page 
