Test Run
Automate Testing of Your Stored Procs
James McCaffrey
Code download available at:TestRun0409.exe(124 KB)
Contents
The Stored Procedure to Test
Test Automation Structure
Preparing the Test Automation
Running the Test Automation
Automating the Automation
Conclusion
Many Windows®-based applications have a SQL Server™ back-end component that contains stored procedures. Although techniques to automatically test functions in the front-end code are well known, the techniques to write test automation for stored procedures are not. SQL Server 2005 will provide greatly enhanced integration with the Microsoft® .NET Framework including the ability to write stored procedures in C# and other .NET-targeted languages. This will certainly increase the use of stored procedures and the importance of testing them thoroughly.
In this column I'll walk you through the creation of a simple but representative SQL stored procedure and will then show you how to quickly write powerful test automation that verifies its functionality so that you'll be ready when you start using SQL Server 2005. I will also explain some key principles of writing test automation for SQL stored procedures.
Any application or service that accesses a database can use stored procedures. In this type of situation you can think of the SQL stored procedures as auxiliary functions of the application. Writing test automation for the individual functions of an application is usually called API testing, and the unit testing techniques for writing such tests are well understood. But as I said, writing test automation for SQL stored procedures is not so well known.
Figure 1** Accessing Employee Database **
The best way to demonstrate what you will accomplish is with two illustrations. Imagine that you are developing an application that accesses a database of employee information. Figure 1 shows a simplistic but representative example of such a database. The button that is labeled List Employees accesses the back-end SQL data using an embedded SQL statement, but the Employee Status Code is retrieved using a stored procedure that is named usp_empStatusCode. This is the stored procedure you want to test. Figure 2 shows a test run that does just that.
Figure 2** Stored Procedure Test Output **
In this example, the test automation feeds employee IDs to the stored procedure programmatically, grabs the actual status code return value, and compares it with an expected value to determine if the test case passes or fails. Results are written to a text file. We only have six test cases here; in a production environment you would likely have many thousands of cases, of course.
Behind the scenes, a SQL database named dbProseware contains a table of employee information named tblEmployees. The example application calls a stored procedure usp_empStatusCode by first connecting to the database and preparing the stored procedure call, as shown in the following code snippet:
string connString = "server=(local);database=dbProseware;Integrated Security=SSPI"; SqlConnection conn = new SqlConnection(connString); conn.Open(); SqlCommand cmd = new SqlCommand("usp_empStatusCode", conn); cmd.CommandType = CommandType.StoredProcedure;
Then the application sets up the stored procedure return value and the single input parameter:
SqlParameter p=cmd.Parameters.Add("ret_val", SqlDbType.Int, 1); p.Direction = ParameterDirection.ReturnValue; p=cmd.Parameters.Add("@empID", SqlDbType.Char, 3); p.Direction = ParameterDirection.Input; p.Value = textBox1.Text;
And finally the app calls the stored procedure, gets the return value, and displays it in the application, as shown here:
cmd.ExecuteNonQuery(); int code = (int)cmd.Parameters["ret_val"].Value; textBox2.Text = code.ToString();
Even though using stored procedures in an application sometimes requires some extra programming effort, stored procedures are less susceptible to script injection attacks, often provide better performance, allow for more sophisticated multi-statement logic, and can frequently create better program structure.
Manually testing this stored procedure through the application's UI would be extremely tedious, time consuming, and error prone. And every time there was a change in the product code, you'd have to test all over again. So let's test the stored procedure by programmatically sending test case data to it and examining the return values as shown in Figure 2. Let me emphasize that we are not testing the entire system; we are just testing the stored procedure component of the application.
The Stored Procedure to Test
In an ad hoc production environment you might just create your back-end database on the fly with the Enterprise Manager program. But it is much better to write a SQL database creation script so you can recreate the database whenever you need to. (Although you can generate database creation scripts in Enterprise Manager from existing databases, the resulting code is usually clunky.) The script in Figure 3 creates a database named dbProseware, then creates a table named tblEmployees, and a user-defined stored procedure called usp_empStatusCode.
Figure 3 Database Creation Script
-- makeDbProseware.sql use master if exists (select * from sysdatabases where name = 'dbProseware') drop database dbProseware go create database dbProseware go use dbProseware create table tblEmployees ( empID char(3) primary key, -- employee ID empLast varchar(35) not null, -- last name empDOH datetime not null, -- date of hire empDOT datetime null -- date of termination ) go -- "Development data" set nocount on insert into tblEmployees values('111','Adams','06/25/2001',null) insert into tblEmployees values('222','Baker','07/25/2002','08/31/2003') insert into tblEmployees values('333','Chung','08/25/2003',null) insert into tblEmployees values('444','Davis','09/25/1999',null) go create procedure usp_empStatusCode @empID char(3) as declare @empDOH datetime, @empDOT datetime select @empDOH = empDOH, @empDOT = empDOT from tblEmployees where empID = @empID if @@ROWCOUNT = 0 return 0 -- no such employee if not @empDOT is null return 1 -- not currently employed if @empDOH < '01/01/2000' return 2 -- old timer if @empDOH >= '01/01/2000' return 3 -- newbie go
After checking to make sure that the database doesn't already exist, I create a database named dbProseware and a table named tblEmployees. Next, I insert some developer data—data used during the development process to be able to perform some basic functionality testing.
The last part of the script creates the stored procedure, usp_empStatusCode, that we'll be testing. It accepts an employee ID and returns 0 if the ID is not valid, 1 if the employee has any termination date (meaning they are not currently employed), 2 if the employee was hired before January 1, 2000, and 3 if the employee was hired after January 1, 2000. Note that this stored procedure is not necessarily an example of good coding technique, but I wanted to keep the code short.
If your background is primarily in procedural programming, you probably tend to think of SQL stored procedures as quite a bit like functions in a traditional programming language. But SQL stored procedures are significantly different from regular functions because in most cases they have a logical dependency on a table or other database object. In this example, notice that the return value from the usp_empStatusCode stored procedure depends completely on the data in table tblEmployees. This fact makes testing SQL stored procedures somewhat different from testing regular functions, as we will see.
Test Automation Structure
There are many ways to structure test automation for SQL stored procedures. The particular structure I will show you has been used successfully on several large-scale projects, but there are important alternatives that I will mention as we go along. My test automation structure is centered around three SQL scripts: makeDbProseware.sql, prepTestAuto.sql, and runTestAuto.sql. In fact, the BAT file that generated the test run output shown in Figure 2 is nothing more than three calls to osql.exe with these scripts as inputs. (The osql utility allows you to enter T-SQL statements, system procedures, and script files from a Windows command prompt.)
You saw makeDbProseware.sql in the previous section. It creates a database, dbProseware, that holds the stored procedure under test, usp_empStatusCode, and an associated table, tblEmployees.
The prepTestAuto.sql script adds two key components necessary for the test automation into the dbProseware database. The first additional component is a test automation utility stored procedure that I named tap_ResetState, which creates a known initial state by populating table tblEmployees with rich employee data to test against. The second additional component is a table of test case data named tblTestCases, which contains test case IDs, test case inputs, and test case expected results. Each row of tblTestCases corresponds to exactly one test case.
The runTestAuto.sql script does most of the work. It iterates through the test case table, tblTestCases, one row at a time using a cursor, calling the stored procedure under test for each row of data. The return value is captured and compared with the expected result to determine a pass or fail result. After the test case result is known, it is logged to an external results file.
Figure 4** Test System **
The diagram in Figure 4 shows how the various parts of the test automation system work together.
Preparing the Test Automation
The test database preparation script prepTestAuto.sql, shown in Figure 5, augments the development database by adding a stored procedure that will reset the database state. This is necessary for two reasons. First, developer data is rarely rich enough for thorough testing of the stored procedure that depends on it, so we need to add richer data. Second, stored procedures often make changes to their database, so we need a way to reset the database to a known initial state. For example, a stored procedure may insert data into or delete a row from a table. Even though the usp_empStatusCode stored procedure in this example does not change the database state, it is better to be safe than sorry.
Figure 5 Test Automation Preparation Script
use master if not exists (select * from sysdatabases where name='dbProseware') raiserror('Fatal Error: required database dbProseware does not exist', 16, 1) go use dbProseware go if exists (select * from sysobjects where name='tap_ResetState') drop procedure tap_ResetState go create procedure tap_ResetState as if not exists (select * from sysobjects where name='tblEmployees') raiserror('Fatal error: tblEmployees not found in dbProseware', 16, 1) -- state data truncate table tblEmployees set nocount on insert into tblEmployees values('111','Adams','01/01/2001',null) insert into tblEmployees values('222','Baker','01/01/2001','01/01/2002') insert into tblEmployees values('333','Chung','01/01/1999',null) insert into tblEmployees values('444','Davis','12/31/2000',null) -- etc. (typically several hundred values) go if exists (select * from sysobjects where name='tblTestCases') drop table tblTestCases go create table tblTestCases -- test cases to test usp_empStatusCode ( caseID char(4) primary key, empID varchar(10) not null, expectedResult int not null ) go set nocount on insert into tblTestCases values('0001','111',3) -- newbie insert into tblTestCases values('0002','222',1) -- not employed insert into tblTestCases values('0003','333',2) -- old-timer insert into tblTestCases values('0004','444',3) -- newbie insert into tblTestCases values('0005','555',3) -- deliberate error insert into tblTestCases values('0006','666',0) -- invalid empID -- etc. (typically several thousand cases) go
As you can see in Figure 5, the preparation script creates a stored procedure named tap_ResetState that deletes all data in the table tblEmployees, then populates the table with many test employees. Next, the preparation script creates a table of test case data and populates it. Notice that I hardcoded both the rich state data and the test case data into the preparation script; a more flexible alternative is to import these data sets from external files using the Bulk Copy Program (BCP) or the Data Transformation Services (DTS) program. Also, I declared the empID test case field as varchar(10) rather than char(3) to allow for test input larger than what the stored procedure may be expecting.
Running the Test Automation
Let's examine the code that actually performs the automated testing of the usp_empStatsCode stored procedure. The code in Figure 6 is the core test automation.
Figure 6 Test Automation Script
-- runTestAuto.sql use master if not exists (select * from sysdatabases where name='dbProseware') raiserror('Fatal Error: database dbProseware does not exist', 16, 1) go use dbProseware go -- core test automation -- create a cursor into the test cases table declare testCaseCursor cursor fast_forward for select caseID, empID, expectedResult from tblTestCases order by caseID declare @caseID char(4), @empID varchar(10), @expectedResult int, @actualResult int declare @fsoHandle int, @fileID int declare @resultLine varchar(80) -- open file to write results to exec sp_OACreate 'Scripting.FileSystemObject', @fsoHandle out -- create object exec sp_OAMethod @fsoHandle, 'OpenTextFile', @fileID out, 'C:\TestStoredProcs\results.txt', 8, 1 -- append, create if necessary -- write header lines to results file exec sp_OAMethod @fileID, 'WriteLine', null, ' ' exec sp_OAMethod @fileID, 'WriteLine', null, 'sp under test = empStatusCode' exec sp_OAMethod @fileID, 'WriteLine', null, ' ' exec sp_OAMethod @fileID, 'WriteLine', null, 'CaseID Result empID Actual Expected' exec sp_OAMethod @fileID, 'WriteLine', null, '=====================================' open testCaseCursor exec tap_ResetState -- initialize state -- priming read of test case data fetch next from testCaseCursor into @caseID, @empID, @expectedResult while @@fetch_status = 0 -- loop while last fetch of test case data was successful begin exec tap_ResetState -- truncate table tblEmployees and restore state data -- execute stored procedure under test exec @actualResult = usp_empStatusCode @empID if (@actualResult = @expectedResult) -- determine Pass or Fail set @resultLine = @caseID + ' Pass ' + @empID + ' ' + cast(@actualResult as char(1)) + ' ' + cast(@expectedResult as char(1)) else set @resultLine = @caseID + ' *FAIL* ' + @empID + ' ' + cast(@actualResult as char(1)) + ' ' + cast(@expectedResult as char(1)) exec sp_OAMethod @fileID, 'WriteLine', null, @resultLine -- write results fetch next -- next test case from testCaseCursor into @caseID, @empID, @expectedResult end close testCaseCursor -- clean up deallocate testCaseCursor exec sp_OADestroy @fileID exec sp_OADestroy @fsoHandle
After checking to make sure the test database exists, I declare a SQL cursor named testCaseCursor:
declare testCaseCursor cursor fast_forward for select caseID, empID, expectedResult from tblTestCases order by caseID
This cursor will point into the tblTestCases table data and give us the ability to iterate through the table data one row at a time. Notice that cursor variables are not preceded with the @ character as normal variables are. There are different kinds of cursors supported by SQL Server 2000; here I use a fast forward cursor because I do not need to jump around the test case data, and fast forward cursors in general provide the best performance.
Next, I declare my local variables: three variables to hold test case data extracted from tblTestCase data by the testCaseCursor cursor, one variable to store the actual result returned when the usp_empStatusCode stored procedure is called, and three variables that will allow me to write test results directly to an external text file, as you can see in the following:
declare @caseID char(4), @empID varchar(10), @expectedResult int, @actualResult int declare @fsoHandle int, @fileID int declare @resultLine varchar(80)
Although it is also possible to create a table to hold test results, write results into it, and export the results data to an external file, SQL Server supports writing results directly to external files using ActiveX® technology. I use the @fsoHandle variable to store a handle of a file system object. Then the @fileID variable will store an integer ID for the results file obtained from the file system object. I will use the @resultLine variable to hold a line of text representing the result of one test case and write it to the external results file.
I begin the test run by preparing the external results text file:
exec sp_OACreate 'Scripting.FileSystemObject', @fsoHandle out -- create object exec sp_OAMethod @fsoHandle, 'OpenTextFile', @fileID out, 'C:\TestStoredProcs\results.txt', 8, 1
The first statement creates an instance of a FileSystemObject object and stores a reference to it into the @fsoHandle variable. The next line opens the results file for appending. For simplicity, I hardcoded the name and location of the results file, but you will probably want to parameterize this information. The rather mysterious-looking 8 and 1 parameters cause OpenTextFile to open the results file for appending and to create the file if it does not already exist.
After writing some preliminary header information to the results file using the sp_OAMethod stored procedure with a 'WriteLine' parameter, I prepare the cursor for iterating through the test case data and initialize the database to a known state:
open testCaseCursor exec tap_ResetState fetch next from testCaseCursor into @caseID, @empID, @expectedResult
The open statement initializes the variable testCaseCursor to point at the in-memory resultset to which it belongs, in this case tblTestCases. Although not entirely accurate, it is useful to imagine that testCaseCursor is pointing to the top of tblTestCases, just before the first row of data. The fetch next statement retrieves the next row of data (in this case the first row) and stores the retrieved values in the specified local variables by position.
The bulk of the automation is performed with a while loop:
while @@fetch_status = 0 begin -- create known state (call tap_ResetState) -- execute stored procedure under test -- determine pass or fail -- write result fetch next -- next test case from testCaseCursor into @caseID, @empID, @expectedResult end
The @@fetch_status is a global variable that holds the result of the last attempted fetch by any cursor on the current connection. A value of 0 indicates the last fetch was successful (values of -1 or -2 indicate failure). So we can use @@fetch_status to determine when we have iterated through the entire resultset pointed at by our cursor. The processing inside the loop is actually quite simple:
exec tap_ResetState exec @actualResult = usp_empStatusCode @empID
We call the helper stored procedure tap_ResetState to create a known state. In particular, we delete all rows of table tblEmployees and then repopulate with rich test data. Now we can execute the stored procedure under test, passing in the test case input data fetched by the cursor, and then capture the actual return value. The location of the call to the tap_ResetState stored procedure is very important. Notice I call tap_ResetState inside the while loop, which means that it is called repeatedly, before every test. In this example, the stored procedure under test, usp_empStatusCode, does not alter the state of the dbProseware database so we could just call it once outside the while loop to improve performance. However, my experience has shown that in most cases it is better not to make any assumptions about what a stored procedure does to system state and to always recreate state inside the main automation code's while loop. The performance hit that is incurred by recreating state before each and every test call is usually a small price to pay for greatly increased test validity.
To determine whether a test case passes or fails, we compare the actual result obtained by calling the stored procedure under test with the expected value fetched from the test case table. In essence, I could simply write the following code:
if (@actualResult = @expectedResult) set @resultLine = @caseID + ' Pass ' else set @resultLine = @caseID + ' FAIL '
Then I would write the result to the results file like this:
exec sp_OAMethod @fileID, 'WriteLine', null, @resultLine
Of course, you will want to display more descriptive information in a results file as I did. The script runTestAuto.sql finishes by cleaning up the resources it allocated so that we don't leak memory with each run.
Automating the Automation
Now that our database creation script, automation preparation script, and automation run script have been written, we can execute them in sequence from within the Query Analyzer program. Another option is to write a short BAT file that issues osql.exe commands to run the three scripts:
@echo off rem runTests.bat osql -S(local) -E -i makeDbProseware.sql -n > nul osql -S(local) -E -i prepTestAuto.sql -n > nul osql -S(local) -E -i runTestAuto.sql -n > nul echo. echo Test run complete echo.
The -E switch makes osql request a Windows Authentication connection using your current Windows login, and is the most secure option for connecting to an instance of SQL Server. The -n switch suppresses line-numbering output and I also suppress miscellaneous messages by redirecting output, with > nul to create a tidier output display. Of course, in a production environment you will want to see all the information you can get. After we construct a BAT file such as the one shown previously, we can easily schedule the test scripts to automatically start by using the Windows Scheduler or by using the Jobs feature of SQL Server Agent, which we can easily set up using Enterprise Manager.
Conclusion
In addition to the technical issues you face when writing test automation for SQL stored procedures, you must solve build management issues, too. In a small production environment, you will probably be aware of changes in the product's code base or changes such as where a new build's code base is dropped. But in a large production environment, changes like these can be surprisingly difficult to manage. I vividly remember working on test automation for a large product and discovering that for three days I had been testing the wrong branch of the build tree! Test team management is outside the scope of this column, but it is something to keep in mind while developing your test automation structure.
It is important to remember that the testing described in this column is just one of the ways to test an application that has a SQL component. For thorough testing, you must test all layers that make up the system. You need to test the application through its user interface, through the application-SQL interface, and through any other interface the system might have (HTTP, sockets, named pipes, services, and so on). Also, the technique presented here tests only SQL stored procedure functionality. You also need to consider stress testing, performance testing, and the like. Many of these areas of testing can also be performed using the basic structure presented in this column.
Send your questions and comments for James to testrun@microsoft.com.
James McCaffrey works for Volt Information Sciences, Inc., where he manages technical training for software engineers working at Microsoft. He has worked on several Microsoft products including Internet Explorer and MSN Search. James can be reached at jmccaffrey@volt.com or v-jammc@microsoft.com.