As I mentioned, with white-box testing, you check for errors along each logical path through a stored procedure. Practically speaking, this means testing all possible combinations of true/false conditions created by IF, GOTO, and other branching statements. (Looping structures create an additional layer of logical complexity that you can explore later.)
As you can see in Listing 2, usp_lookupPrice contains two IF statements (in lines 6 and 8). Each condition first checks for potential errors and, if it finds one, returns a unique user-defined status code to the calling procedure. When you combine them, the two branching statements define three separate paths through the T-SQL code in usp_lookupPrice:
- Path 1: statements 5, 6, 8, and 10
- Path 2: statements 5, 6, and 7
- Path 3: statements 5, 6, 8, and 9
Table 2, page 26, shows the testing details of the three paths. Path 1 tests usp_lookupPrice's core business logic—namely, setting @product_price equal to a valid product_price and returning a status code of 0. Paths 2 and 3 test the two error handlers. Path 2 returns a status code of 10 and an error message of Row not found; Path 3 returns a status code of 11 (Unit_price is null) along with a null unit_price.
To test all three paths requires a separate test case for each. The test case for Path 1 covers the normal state, in which requesting a valid record (1, 2, 3, or 4) returns a status code of 0 and sets @product_price to the product_price value for the corresponding record. For Path 2, @@rowcount will be 0 whenever @product_id requests an invalid product_id from the Products table. Any product_id greater than 5 meets this condition. So, running a test using 6 as the @product_id should produce the expected outcomes for Path Statement 2 in Table 2—namely, a return value of 10 and a NULL output parameter value.
Finally, for Path 3, recall that record 5 in the Products table contains no product_price—a common bug I've recreated for this example. Requesting a product_price for record 5 will have two expected effects—a return value of 11 and a NULL @price output parameter.
Step 3: Run the Test, Verify the Outcome
Before running your tests, gather all the information and other resources you need, including the expected outcomes, the test cases, any special instructions for conducting the tests (e.g., what order to run the tests in), and any special setup requirements (e.g., log into the database as tester) and clean-up operations. You also need some way to feed input values to the procedure (I'm using Query Analyzer in my examples) and a spreadsheet, data table, or automated test manager for logging test results.
Sometimes, you'll run a test once and never repeat it. But you'll discover that you rerun most of your tests many times. Because an effective test is one that uncovers bugs, your first test run should usually be followed by a second to check your bug fixes. Thereafter, every code change you (or others) make should be followed by another test-debug-retest cycle.
To maintain consistency across cycles, you need a written script that tells the tester how to set up and run the test and record the results. For tests with many setup requirements or execution steps, the test script should include detailed instructions that stand alone. When preparing your test scripts, supply enough detail for someone else to carry out the test without having to ask for additional information or undergo special training. To save time and improve consistency from test to test, reuse the same scripts whenever possible.
Table 3 contains a sample script for testing the usp_lookupPrice procedure. Steps 3.1 through 3.3 are essential setup operations for all unit-level procedure tests. Step 3.4 verifies that a successful database connection has been made and that the correct data has been loaded into the test tables.
Step 3.5 shows a test driver you can use to feed test values to the stored procedure you're testing. We're running our tests directly from Query Analyzer here, but if you're doing a lot of testing, you might consider writing a stored procedure to automate this process. Another option is to run your test from a Web page or other client application. At this early testing stage, it's best to minimize outside interference—like a dead Web server—by staying as close to the code module as possible.
Finally, Step 3.6 executes the test, documents the results, and records the test outcomes. In this case, you can simply look at the query results to be sure the expected outcomes occurred. If the input data passes the test, you can note that fact and move on to the next test. For procedures that contain INSERT, UPDATE, or DELETE operations, you also need to verify that all test records have been correctly added, modified, or removed from the proper tables.
When the correct outcome doesn't occur, you need to record that result, as I discuss later. Include all relevant details about the actual outcome such as any error messages returned or any potential problems, like a broken connection. This information tells whoever debugs the procedure where to begin looking for likely errors. It also lets you know after you run a post-debug test whether you've fixed the bugs that caused the initial test to fail.
Prev. page
1
2
[3]
4
next page 
