Finally, to wrap up the test cases for usp_lookupPrice, let's check the results for the two remaining scenarios in Table 2. Test Case 2 requires a product_id greater than 5, so let's request product_id 6 to test the error-handling code that captures out-of-bounds product IDs. Using the test driver from Step 3.5, run the following script from Query Analyzer:
DECLARE @price money, @return_
value int
EXEC @return_value=usp_
lookupPrice 6, @price OUTPUT
SELECT @price "Unit Price"
SELECT @return_value "Return Value"
As expected, this request causes usp_lookupPrice to fail and returns a status code of 10—along with a NULL output parameter—thus verifying the correctness of the error-handling code.
Requesting product_id 5 also causes an expected fault and returns a status code of 11, indicating an invalid price (to run this test, just replace the 6 with a 5 in the preceding script). Again, the test results show that the error-handling code is behaving correctly.
Step 4: Document Your Test Results
Documenting your results is an essential part of the testing process. So, depending on your test outcome, you need to record your findings in at least one of two places. First, if your test succeeds, record that fact in a test log. A test log need not be a complex document; keep it as simple as possible, or you'll probably never use it. You can create a test table in your database or, better yet, create a dedicated database for your testing work. Begin by assigning a unique, unchanging number to each test you run. In the test log, also include all relevant information—the test conditions, the test values, the expected outcomes, the person who designed the test, the name of the procedure being tested, and the test script's file location.
Because you'll be running most tests multiple times, consider using a separate TestDetail table as a running record of each testing cycle. Besides a test_id foreign key, the detail table should include the state of the test (e.g., scheduled but not yet run or run but not yet completed), the date and time the test was run, the person running the test, the test outcome, the test result, and any follow-up action to be taken. Follow-up actions might include rerunning the test, debugging the procedure, or analyzing the test conditions when you suspect something went wrong with the test.
If the test fails, you still need to record that fact in your test log. In addition, you need a second document for tracking how you respond to test failures. This document is commonly known as a software problem report (SPR) or a bug tracker. When you get to system-level testing, SPRs are usually tracked by professional testers (if you have them) and become official entries in your project's change-management system. At the unit-testing level, the main purpose of an SPR is to help you, the procedure developer, better organize your testing and debugging work.
Again, you'll probably want to create a separate tracking table to store the data that goes into your SPR. That data should include the type of error (e.g., coding, design, hardware), the error severity (e.g., fatal, serious, minor), and the nature of the error as well as how to reproduce it. You should also date and summarize the problem in a separate column; the summary is the information your manager will most want to see. In addition, your SPR should include fields for tracking whatever follow-up activity takes place, the problem's status (open or closed), who resolved the problem, who retested the stored procedure, and the date each of those events occurred.
Step 5: Retest as Needed
Obviously, finding bugs is only half the story. Once you've uncovered an error, you must locate its source and rework the responsible code to remove the error. When retesting a bug fix, repeat the original test exactly as it was designed and previously executed. Variations in retest procedures can easily mask incomplete or improper fixes. They can also mislead you into thinking a valid fix has failed or created undesirable side effects. The first all-nighter you pull looking for phantom bugs will be the last time you question the need for consistency across the test-debug-retest cycle.
Procedure testing is an essential development practice used worldwide by quality-conscious DBAs and database developers. The testing framework I've outlined can dramatically improve the reliability of your database applications. Mastering unit-testing practices and skills will make you a better DBA and give an extra boost to your career as a SQL Server database professional.
End of Article
Prev. page
1
2
3
[4]
next page -->
