Picture this: You've just finished debugging your final stored procedure for the department's latest SQL Server application. You're ready to check in your work and call it a day—or are you? Are you sure your T-SQL code will stand up to users' demands? What about the functionality you've designed into your code? Have you covered every business requirement? Does each function deliver as advertised under all normal operating scenarios?
Even if you can answer "yes" to all those questions, it's not time to relax. What about the outcomes you hope won't occur? Have you tested common error conditions that caused problems in the past? And what about the error handlers? Are you sure they're free of bugs? If you find yourself falling short in any of these areas, you might want to reassess the way you unit-test your stored procedures.
Unlike system tests that professional testers conduct after an application is "code complete," unit testing looks for errors in individual modules, such as stored procedures, while those modules are being developed. Unit testing isn't difficult, but to work effectively, it requires planning, documentation, and above all, a shared understanding of some basic principles. So before jumping into the testing process, let's first dispel a few common myths about unit testing that often get in the way of its proper use.
Myth 1: Testing is just another name for debugging. Testing looks for errors and reports their occurrence; debugging tries to find what caused the error, then corrects the defective code.
Myth 2: Testing is used to show a program is free of errors. Your testing goal should be to find errors, not to prove they don't exist.
Myth 3: Testing is the job of professional testers. Testing should be done in stages and involve programmers and professional testers. The first stage, unit testing, is the programmer's responsibility. After your code has passed its unit tests, it's ready for system testing by a quality-control professional.
Myth 4: Testing is an end-of-coding task. For best results, you need to plan how to test every piece of code you write; designing the tests should ideally come before—not after—you start coding.
Myth 5: Formal testing is unnecessary for stored procedures. To some DBAs, testing means throwing a few parameter values at a procedure and declaring victory when the query executes cleanly. Unfortunately, such throwaway tests are hard to debug, aren't reusable, and waste time. Formal testing takes discipline, but that's the price of improved reliability.
Front-Line Testing
So how should you test your stored procedures? You first unit-test each procedure by feeding it appropriate test data, observing the outcome, and retesting as needed to make sure the bug fixes contain no new errors. Unit-testing a stored procedure involves a series of test-debug-retest cycles. The number of cycles will depend on your company's defect tolerance, your application's release date, and, of course, the "bugginess" of your code.
Each of your unit tests should follow these five steps:
- Define the expected outcomes.
- Prepare the test data.
- Run the test and verify the outcome.
- Document the results.
- Retest as needed.
Over time, you'll compile a library of tests that you can reuse for other procedures, share with other developers, and adapt to future development projects. As your test library grows, so will your testing prowess. The result will be a quicker, more effective testing regimen, uniquely suited to your (and your organization's) development needs and style.
Keep in mind that unit testing is just one phase of testing. After you've unit-tested a procedure, you (or your "build manager," if you have one) might combine it with other parts of your application such as a business object or a UI component such as an Active Server Pages (ASP) page. Integration testing at that level is beyond the scope of this article, but in an upcoming article, I'll cover an early step that often occurs in that process—integration testing of two procedures.
To examine the unit-testing process, let's walk through the steps of a practical example of testing a procedure that looks up a product price, then adds a record to an Orders table. The sample procedure requires a Products table and an Orders table, which you can create by running the script that Listing 1 shows. You can implement the business logic as a single procedure, but to better observe the testing process, let's separate it into two procedures. This article covers the inner procedure, usp_lookupPrice, which looks up prices in the Products table. My next article will cover testing of the outer procedure, usp_insertOrder, which uses the inner procedure to prepare an order record.
Prev. page
[1]
2
3
4
next page 
