Step 1: Define the Expected Outcomes
Every stored procedure has some kind of outcome, if only a return value or a system error message. Most procedures also perform database operations that produce other kinds of outcomes; obvious examples include INSERT procedures that add table records and SELECT procedures that generate result sets. Stored procedures can also generate custom error messages or status codes as well as return values, print statements, or output parameters. The lookup procedure, usp_lookupPrice, which Listing 2 shows, can produce all these different outcomes. The procedure searches for a product, sets an output parameter equal to the product's unit price, then returns a default status code of 0, indicating successful execution.
Let's start defining outcomes by looking at the output parameter, @product_price. The first expected outcome is the product price being returned to the calling program. To better see what's happening in this test scenario, use the code Listing 3 shows to populate the Products table with data. You can then use Listing 4's code to execute the usp_ lookupPrice procedure.
Depending on the product_id you use to test usp_lookupPrice, the expected outcome for @product_price will be $19.95, $29.95, $39.95, $11.00, or NULL. But what if you select an invalid product_id? The result will depend on the test value or other keyboard character you feed to usp_lookupPrice. For example, inputting an invalid integer, say 11, as the product_id will evoke a NULL return value. Entering an alphabetic character such as a or f will generate a Server: Msg 8114 error message: Error converting data type nvarchar to int. And failing to enter a product_id or entering a special character such as & will cause a Server: Msg 170 error message: Incorrect syntax near ','. So, depending on your test values for product_id, you can expect the execution of usp_lookupPrice to result in a valid product_price (when product_id is 1, 2, 3, or 4), a null product_price, or a T-SQL message signaling a conversion error, a syntax error, or other system error.
Now, what about the return statements in usp_lookupPrice? You can use a return statement to pass error-handling control to a calling program or procedure by assigning custom integer status codes to different error conditions. You can program the calling process to handle the code resulting from an encounter with one of these status codes however you choose, including by ignoring it.
The expected outcome for a return statement is 0—the default status code for a procedure that executes successfully. Besides 0, a return statement can return Microsoft-reserved codes of -1 to -99, as well as any custom codes you define. Usp_lookupPrice contains two custom status codes, 10 and 11, so the expected return values are
- 0, when no errors or warnings occur
- 10, when no unit price is found
- 11, when the unit price is null
- -1 to -99, when usp_lookupPrice raises a reserved status code
Step 2: Prepare the Test Data
The key to effective unit testing is choosing the right starting conditions. In most cases, these starting conditions are a combination of test values you assign to a procedure's input parameters. Together with the expected outcomes, the values you assign to these parameters constitute a test case. But beware—a good test case requires that you choose the right combination of test values.
Testing experts have devised many ways (sometimes overlapping) of choosing test values. The classic distinction is between white-box and black-box tests. White-box, or code-based, tests use test values that probe for errors along each logical pathway in a block of code. Black-box, or specification-based, tests use test values that verify that a procedure correctly performs its assigned responsibilities, such as inserting or deleting a record. Table 1 summarizes these and other common test-case design techniques.
You can create test cases from any or all of these techniques. The more techniques you tap, the more comprehensive your tests will be. Just remember: Every new test case takes extra time to design, program, execute, and analyze. Consequently, many development shops limit how many and in what order tests should be run. If your team hasn't yet established such rules, you might want to start.
When you're unit-testing stored procedures, starting with white-box testing techniques is usually best because procedure developers are the ones with the best overall grasp of the code. Also, unlike professional testers in many shops, developers usually have direct access to their own procedures. So if you (the programmer) don't do it, white-box testing will probably be ignored. Time permitting, you can augment your white-box tests with other techniques, but your T-SQL code is where you need to concentrate your unit-testing efforts.
Prev. page
1
[2]
3
4
next page 
