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.
End of Article
Prev. page
1
[2]
next page -->
