| Downloads |
|---|
| 43714.zip |
Integration testing your T-SQL stored procedures saves debugging time and makes database programs more reliable and easier to reuse and maintain. In these respects, integration testing has many of the same benefits as unit testing individual procedures. The big difference between the two testing methodologies is that unit testing focuses on the internal logic of one procedure, and integration testing tries to identify problems that might happen when one procedure (the parent or "outer" procedure) calls another procedure (the child or "inner" procedure). Another important difference is that integration testing usually follows unit testing. After ridding two or more procedures of any internal defects, you can integration-test the procedures by checking for defects in the outer procedure's call statements and in any data or error messages the inner procedure returns to the outer procedure.
In my February 2003 article, "Unit-Test Your Stored Procedures" (InstantDoc ID 37428), I showed how to unit-test a stored procedure in five easy steps:
1.Define the expected outcomes.
2.Prepare the test data.
3.Run the test; verify the outcome.
4.Document your test results.
5.Retest as needed.
To illustrate these steps, I used a procedure that looks up a product price, then adds a record to an Orders table. I divided this logic into two smaller procedures. Listing 1 shows the inner procedure, usp_lookupPrice, which looks up prices in a Products table.
Having unit-tested the inner procedure, let's now unit-test the outer procedure that calls the inner procedure. Listing 2 shows the outer procedure, usp_insertOrder, which uses the inner procedure to prepare an order record. To complete the testing process, you need to integration-test the two procedures by checking for errors when the outer procedure calls the inner procedure. To carry out the necessary tests, you need a Products table, an Orders table, and some Products data. Listing 3 generates these. Now that you've created the necessary objects, let's walk through the unit-testing process for the outer procedure, usp_insertOrder, then look at how to test the integration of the inner and outer procedures.
Unit Testing the Outer Procedure
Following the five unit-testing steps I outlined, let's start the testing process with the expected outcomes. As Listing 2 shows, usp_insertOrder obtains a product price from usp_lookupPrice, inserts a record into the Orders table, then sets an output parameter equal to the new record's order_id value. Under normal conditions—that is, when the usp_insertOrder procedure runs without errors—usp_insertOrder returns a default status code of 0. The new record it inserts into the Orders table has an expected order_id value equal to the previous order_id + 1, a quantity value equal to the input parameter @quantity, a unit_price value equal to the output parameter @price (which usp_lookupPrice retrieved from the Products table and passed to the outer procedure), and an item_cost value equal to the product of @quantity and @product_price.
But when usp_lookupPrice encounters an error, usp_lookupPrice passes usp_insertOrder a return code indicating the source of the error it encountered. In response, usp_insertOrder passes an error message (such as "Invalid ID," "Missing price," or "Unknown error") to its parent process and returns a status code of 99 (an error code I devised for this example).
In moving on to the second testing step, preparing the test data, you first need to determine the flow of control through usp_insertOrder. Your goal is to trace all logical paths through the code so that you test every instruction at least once. Each branching condition in a procedure creates a new set of paths, and nested conditions further multiply the possibilities. So to be sure you've covered every possible path, you must carefully trace the flow of control resulting from usp_insertOrder's four IF conditions. Figure 1's analysis of this procedure shows five distinct paths SQL Server can take through the procedure:
- Path 1: statements 1-3, 14-16, and 18-19
- Path 2: statements 1-6
- Path 3: statements 1-5 and 7-9
- Path 4: statements 1-5, 7-8, and 10-11
- Path 5: statements 1-3 and 14-17
Path 1 follows usp_insertOrder's core business logic—inserting a new record into the Orders table, setting @order_id equal to the new record number, and returning a status code of 0. The other four paths cover the error handlers in Listing 2 that manage some common exceptions that can occur. Path 2 calls usp_lookupPrice, which returns a status code of 10 when usp_insertOrder passes it an invalid product_id. In Path 3, the inner procedure passes the outer procedure a status code of 11 when usp_lookupPrice can't find a matching price for the requested product_id. Path 4 returns an "Unknown error" message when usp_lookupPrice fails to execute for some reason other than an invalid product_id value. And Path 5 returns a status code of 12 (insert error) if usp_insertOrder fails to insert a new order.
Table 1 contains input conditions that you can use to test these five paths, along with the expected outcomes under each scenario. To test Path 1, you need a @product_id value that has a valid product_price and a @quantity value greater than 0. To test Path 2, you need an invalid, non-negative @product_ID and a @quantity value of 1 or more. As an outcome of these test values, you'd expect your procedure to return a value of 99 and an "Invalid ID" error message. For Path 3, which tests for a missing price, you need a @product_ID value of 5, corresponding to a null product_price (given the records in the example Products table). Path 4 traps unknown error conditions unrelated to input parameters or table values, so a unit-level test case isn't appropriate in this instance. Bad network connections, a busy server, or a permissions problem can sometimes trigger such errors, but these are system-level testing scenarios beyond the scope of this article. Finally, you can use an invalid @quantity value to test for an insert failure along Path 5. Setting @quantity to a negative value violates the check constraint on the Products table's quantity field, returns a status code of 12, and cancels the INSERT operation.
Now you're ready to run the unit tests and verify the outcomes as you did with the usp_lookupPrice procedure in the February 2003 article. This time, use the test driver that Listing 4 shows. After all test cases pass the tests, you can document the results and move on to the integration-testing phase.
Integration Testing Two Procedures
Once you've tested each procedure individually, you need to verify that related procedures work together as they should. You don't need to integration-test every procedure—just those that invoke other procedures. (Procedures that support client applications and middleware objects—such as DLLs, extended stored procedures, and COM objects—also require integration testing, but testing at that level is usually the job of professional testers.) For procedures that need it, integration testing checks for interface errors. Checking for interface errors means that you must verify in your tests that each outer procedure correctly invokes its inner procedures. In most cases, this testing takes only three steps.
First, test the outer procedure's call statements—the ones that invoke inner procedures—for the proper number, spelling, placement, and data typing of parameters. You can execute these tests dynamically by verifying appropriate test cases as I described or—what's often more cost-effective—by statically inspecting the syntax and semantics (e.g., the correct parameter names) of each DECLARE and EXEC statement. Is the number of input and output parameters the same? Are all parameters assigned the correct data types? Are the parameters correctly ordered? If the answer to any of these questions is no, fix the problems and reinspect your code until you reach an error-free state. Inspections are an efficient verification tool, especially when you collaborate with knowledgeable teammates and use standard checklists that cover the relevant interface concerns—parameter number, order, and so on.
After you've verified the calls to your inner procedures, you can move on to Step 2. You need to test how your outer procedure uses return data, especially output parameters and status codes (both custom and system-defined). You've already completed some of this work if you followed the preceding plan and included error conditions as expected outcomes in your unit tests. Sometimes distinguishing whether a given test is a unit test, an integration test, or a little of both, can be difficult because some overlap across test cases is inevitable. However, what you call the test isn't as important as the process of testing how well two or more procedures work together.
Finally, after successfully completing steps 1 and 2, rerun the unit tests for the outer procedure. Do all unit tests still execute cleanly? If not, debug the procedure as necessary and repeat all previous tests until they do. As you progress in your integration testing, you'll notice that some tests work better than others at uncovering errors. Be sure to include these high-yielding tests when you regression-test your bug fixes to make sure those fixes don't introduce any new defects into your code. Regression testing is an industry best practice for ensuring software reliability and is appropriate at all testing levels—unit, integration, and system.
Note that when you're testing simple procedures such as usp_insertOrder and usp_lookupPrice, running your integration tests at the same time you unit-test your outer procedure is usually safe. I've followed that approach in this article because it's the most efficient way of unit testing simple outer procedures. But beware—the more complex the procedure, the harder it is to disentangle logic errors (a unit-testing issue) from interface errors (an integration-testing issue). To avoid the extra debugging and retesting time this complexity will require, you need to properly sequence your tests so that you shield your outer procedure from errors originating inside the inner procedures it calls. The general process of unit testing and integration testing stored procedures has four steps:
1.Unit-test each inner procedure.
2.Isolate your outer procedure from your inner procedures.
3.Unit-test your outer procedure.
4.Individually integrate each inner procedure, testing for interface errors after each integration cycle.
Step 2 usually involves commenting out (or simply removing) all calls to inner procedures and replacing all return data (output parameters, status codes, and return values) with hard-coded values. For example, Listing 5 recreates usp_insertOrder but comments out all references to usp_lookupPrice and hard-codes @price to a value of 11.00. (To run this script, first drop usp_insertOrder.) After creating the usp_insertOrder procedure, you can use Listing 4's test driver again to run your tests.
Because Step 3 doesn't execute any inner procedures, you'll know that any errors you find are the result of faulty logic in your outer procedure and not of interface errors. Finally, in Step 4, restore the original execution statement for your first (if you have more than one) inner procedure, then integration-test the inner procedure's interface as before. After testing, debug and rerun the tests as needed, then repeat the same process for the next inner procedure.
When should you sequence and isolate rather than piggyback your integration tests on top of your unit tests? Ultimately, that's a judgment call you must make. But keep in mind that complexity grows with each inner procedure you include and with each input and output parameter you add to an inner procedure's interface. And each inner procedure you add to an existing one lengthens the chain of nested procedures SQL Server has to execute in one invocation.