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.