Automated Testing with Up_autoTest
Now that we've loaded tabTestValues with test cases, let's take a closer look at the test driver, the up_autoTest stored procedure that Listing 4 shows. Up_autoTest reflects the realities of real-world test environments. This stored procedure has lots of great features. It's self-contained (in this case, running directly from Query Analyzer), tests any number of procedures in a single run, and runs any number of tests on the same procedure. In addition, the stored procedure works with all commonly used stored procedures (nonparameterized procedures, input-only procedures, and procedures with output parameters), supports any number of input parameters (up to the legal T-SQL limit), and supports all standard T-SQL data types (e.g., int, money, char). Up_autoTest provides a standard format for all tests, captures return values as well as output values, when available, allows procedure testers and developers to work independently of one another, as needed, and is easy to use, maintain, and extend.
To understand the way up_autoTest works, consider how you'd use Query Analyzer to manually test the up_lookupPrice stored procedure that Listing 3 shows. First, you'd declare a local variable—@price—to receive up_lookupPrice's output value. Second, to check for potential error conditions, you'd declare another local variable—@return_value—to receive up_lookupPrice's return value. Third, you'd select a test value—such as 4—for up_lookupPrice's input parameter, @product_id. Finally, you'd include an execution statement, followed by SELECT queries to display @price and @return_value. The following code shows this test process:
DECLARE @price money, @return_value int
EXEC @return_value = up_lookupPrice 4, @price OUTPUT
SELECT @price "Unit Price"
SELECT @return_value "Return Value"
Because the required Products table doesn't exist, executing up_lookupPrice results in a null Unit Price and a return value of 10 (the custom code that signifies a missing table in Listing 3). Because of this fault, you'd conclude that up_lookupPrice failed this particular test.
The question we need to answer is how to automate these manual steps to test up_lookupPrice. TabTestValues already contains all the necessary information: the procedure name, the names and test values of all input parameters, and the names and expected values for all output parameters. So, to automate our testing steps, we simply write T-SQL code that combines this information into test scripts like the one we just created manually, then executes those scripts. The T-SQL code will then compare actual with expected outcomes, capture any custom error codes, determine the test results and record them for later use, and produce a Web report that will display the test results.
Creating the T-SQL code is the meat of the process, but the ability to execute stored procedures (or other T-SQL code) on the fly is what actually makes automated testing possible. By deferring query construction until run time, test designers can store procedure names and test details in a metadata table (such as tabTestValues) without worrying when, by whom, or how often those tests will eventually be run.
To illustrate the automatic test process, let's walk through the sample stored procedure up_AutoTest, which Listing 4 shows, as it dynamically tests up_lookupPrice. First, up_AutoTest identifies up_lookupPrice as the procedure under test by selecting its proc_name and any associated parameters inserted into tabTestValues. Next, up_AutoTest uses a cursor to dynamically build a test script, concatenating the proper execution syntax (the procedure name along with any parameters and placeholder variables needed to receive return values or parameter outputs) into a local variable, @query. Once the test script is complete, up_AutoTest executes @query.
As I designed it, up_AutoTest runs any number of sequential unit tests. Should you need to execute individual procedures, you can revise up_AutoTest to include an input parameter referencing the procedure name. Figure 1 lists the steps in up_AutoTest's logic.
Reporting Test Results
And that's it—well, almost. Our requirements also say we must report our test results. To produce reports, we need another table, tabTestResults, which the code in Listing 5 creates. Now, after executing all the tests, the up_AutoTest stored procedure queries tabTestResults for any return values, parameter outputs, and expected test outcomes. Then, the procedure evaluates the three values and defines a test result. (You might choose to define test results differently than I did.) Finally, the procedure sets the test_result column to Pass or Fail and updates tabTestResults to include the test results.
To keep everyone on top of your team's testing work, up_AutoTest also includes automated Web reporting. Just specify a valid file path in the final block of Listing 4's code, and up_AutoTest queries tabTestResults for the necessary data, then executes the system procedure sp_makewebtask (the brains behind SQL Server's Web Assistant), producing a Web report of your test results. In case you need them, sp_makewebtask offers parameters for customizing Web reports, including job-scheduling options through SQL Server's Agent service.
When you run up_ AutoTest, you'll see from the Test Results Web report that the up_lookupPrice procedure failed the test we designed, returning the same custom error code (10) as before—and thereby alerting us to the missing Products table. The other three procedures all passed their tests, returning a 0 (error-free) execution code to the calling process. Sproc_1 contained no output parameters (hence the None flag in the Actual Output Value column of Test Results), whereas sproc_2 and sproc_3 both passed back expected output values.
Using Up_AutoTest
When using automated testing, keep in mind that test effectiveness depends as much on the test cases you design as the test procedures you follow. Do your test cases force the expected behavior (e.g., triggering an error, updating a column) from your procedure? If not, you'll need to reconsider the input values you're using or the pre-test state of your database (e.g., column values, access permissions). Are you getting back the data (return values, output parameters, table changes) you expect? As yet, up_AutoTest doesn't automatically check the correctness of the SELECT statements' result sets or the table changes that INSERT, UPDATE, or DELETE operations produce. If these checks are important to your testing goals, you can perform them manually or extend up_AutoTest to do the work for you.
Take It for a Spin!
Now, you can automate your stored procedure unit tests. The framework—and associated test driver, up_AutoTest—that we use in this article's examples support many recurring test scenarios. By using up_AutoTest, you can save time when you're unit testing a new procedure, retesting a procedure you (or someone else) previously wrote, debugging procedures and running regression tests to check for unwanted side effects, or unit testing procedures across an entire database or application. Are you ready to see for yourself how useful automated testing can be? Just copy and paste this article's listings into Query Analyzer and give up_AutoTest a test drive. You—and the test specialists on your team—will be glad you did.