SQL Stored Procedure Testing
9.0 Introduction
Many Windows-based systems have a SQL Server backend component. The AUT or SUT often
accesses the database using stored procedures. In these situations, you can think of the SQL
stored procedures as auxiliary functions of the application. There are two fundamental
approaches to writing lightweight test automation for SQL stored procedures. The first
approach is to write the automation in a native SQL environment, meaning the harness code
is written using the T-SQL language, and the harness is executed within a SQL framework such
as the Query Analyzer program or the Management Studio program. The second approach is
to write the test automation in a .NET environment, meaning the harness code is written
using C# or another .NET language, and the harness is executed from a general Windows envi-
ronment such as a command shell. This chapter presents techniques to test SQL stored
procedures using a native SQL environment. The second approach, using C#, is covered by the
techniques in Chapter 11. In general, because the underlying models of SQL and .NET are so
different, you should test stored procedures using both approaches. The techniques in this
chapter are also useful in situations where you inherit an existing T-SQL test harness.
Figure 9-1 illustrates some of the key techniques in this chapter. The figure shows a portion
of a T-SQL test harness (in the upper pane) and sample output (lower pane). The automation is
testing a SQL stored procedure named usp_HiredAfter(). The stored procedure accepts a date
as an input argument and returns a SQL rowset object of employee information (employee ID,
last name, date of hire) of those employees in a table named tblEmployees whose date of hire is
after the input argument date. Although the actual and expected values in this situation are
SQL rowsets, the test automation compares the two using a binary aggregate checksum. Test
case 0002 is a deliberate error for demonstration purposes. The complete source code for the
test harness and database under test shown in Figure 9-1 is presented in Section 9.9.
The script shown in Figure 9-1 assumes the existence of test case data and test result stor-
age in another database named dbTestCasesAndResults. The script tests stored procedure
usp_HiredAfter(), which is contained in a database named dbEmployees and pulls data from
table tblEmployees. When testing SQL stored procedures, you do not want to test against the
development database for two reasons. First, testing stored procedures sometimes modifies
the containing database. Second, development databases usually do not contain data that is

the osql.exe program.
Solution
The following script creates a database named dbTestCasesAndResults containing a table for
test case data, a table for test results, and a dedicated SQL login so that programs can connect
to the database using either Windows Authentication or SQL Authentication.
-- makeDbTestCasesAndResults.sql
use master
go
if exists (select * from sysdatabases where name='dbTestCasesAndResults')
drop database dbTestCasesAndResults
go
if exists (select * from sysxlogins where name = 'testLogin')
exec sp_droplogin 'testLogin'
go
create database dbTestCasesAndResults
go
use dbTestCasesAndResults
go
create table tblTestCases
(
caseID char(4) primary key,
input char(3) not null, -- an empID
expected int not null
)
go
-- this is the test case data for usp_StatusCode
-- can also read from a text file using BCP, DTS, or a C# program
CHAPTER 9
■
SQL STORED PROCEDURE TESTING 239

be directed at the SQL master database, which would be very bad. Now you can create a table
to hold test case input. The structure of the table depends on exactly what you will be testing,
but at a minimum, you should have a test case ID, one or more test case inputs, and one or
more test case expected result values. For the test results table, you need a test case ID column
and a test result pass/fail column at a minimum. If you intend to store the results of multiple
test runs into the table, as is almost always the case, you need some way to distinguish results
from different test runs. One way to do this is to include a column that stores the date/time
when the test result was generated. This column acts as an implicit test run ID. An alternative
is to create an explicit, dedicated test run ID.
CHAPTER 9
■
SQL STORED PROCEDURE TESTING240
6633c09.qxd 4/3/06 2:00 PM Page 240
Comments
SQL databases support two different security modes: Windows Authentication, where you
connect using a Windows user ID and password, and Mixed Mode Authentication, where you
connect using a SQL login ID with a SQL password. If you want the option of connecting to
your test database using SQL authentication, you should create a SQL login and associated
password using the sp_addlogin() system stored procedure. You can drop a SQL login using
sp_droplogin(), after first checking whether the login exists by querying the sysxlogins table.
After creating a SQL login, you need to grant permission to the login to connect to the data-
base. Then you need to grant-specific SQL statement permissions, such as SELECT, INSERT,
DELETE, and UPDATE, on the tables in the database.
A SQL login is easy to confuse with a SQL user. SQL logins are server-scope objects used
to control connection permissions to the SQL server machine. SQL users are database-scope
objects used to control permissions to a database and its tables, stored procedures, and other
objects. When you assign permissions to a SQL login, a SQL user with the identical name is
also created. So you end up with a SQL login and a SQL user, both with the same name and
associated with each other. Although it’s possible to have associated logins and users with
different names, this can get very confusing, so you are better off using the same-name default

T-SQL script myScript.sql as input. The osql.exe line numbering is suppressed (-n), and
miscellaneous shell messages is also suppressed (> nul).
Comments
The osql.exe program, which ships with SQL Server, provides you with an automation-friendly
way to run T-SQL scripts. The -S argument specifies the name of the database server to use. You
can use "(local)" or "." to specify the local machine, or you can use a machine name or IP
address to specify a remote machine. If you want to connect and run your script using SQL
authentication, you must specify the SQL login and the SQL password. If you want to connect
and run using integrated Windows Authentication, you can do so with the -E argument:
C:\>osql.exe -S. -E -i myScript.sql -n > nul
Be careful here because the osql.exe arguments are case sensitive: -E means use Windows
Authentication, whereas -e means to echo the input. In a pure Windows environment, you are
generally better off using Windows Authentication. Mixed Mode Authentication can be trouble-
some because it’s difficult to diagnose problems that arise when there is an authentication or
authorization conflict between the two modes.
A variation on using the osql.exe program to run T-SQL scripts is to use a batch file, which
calls an osql.exe command. For example, you could write a batch file such as
@echo off
rem File name: runMyScript.bat
rem Executes myScript.sql
echo.
echo Start test run
osql.exe -S(local) -E -i myScript.sql -n > nul
echo Done
This approach allows you to consolidate the execution of several scripts, such as a test
harness preparation script, a harness execution script, and a results processing script. With a
batch file, you can also schedule the test automation to run using the Windows Task Scheduler
or the command line at command.
An alternative to using the osql.exe program to run a T-SQL script is to run the script
from the Query Analyzer program. You simply open the .sql file and then use the Execute

8.0
3
1 SQLCHAR 0 4 "," 1 caseID SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 3 "," 2 input SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 2 "\r\n" 3 expected SQL_Latin1_General_CP1_CI_AS
The command to import the test case data is
C:\>bcp.exe dbTestCasesAndResults..tblTestCases in newData.dat
-fnewData.fmt -S. -UtestLogin -Psecret
This command means run the BCP on SQL table tblTestCases located in database
dbTestCasesAndResults, importing from file newData.dat using the mappings defined in file
newData.fmt. The instruction is to be executed on the local SQL server machine, connecting
using the testLogin SQL login and the testLogin user, with SQL password secret.
CHAPTER 9
■
SQL STORED PROCEDURE TESTING 243
6633c09.qxd 4/3/06 2:00 PM Page 243
The key to using this technique is to understand the structure of the format file used by the
bcp.exe program. The first line contains a single value that represents the version of SQL server.
SQL Server 7.0 is version 7.0, SQL Sever 2000 is version 8.0, SQL Server 2005 is version 9.0, and
so on. The second line of the format file is an integer, which is the number of actual mapping
lines in the format file. The third through remaining lines are the mapping information. Each
mapping line has eight columns. The first five columns represent information about the input
data (the text file in this example), and the last three columns represent information about the
destination (the SQL table in this example). The first column is a simple 1-based sequence
number. These values will always be 1, 2, 3, and so on, in that order. (These numbers, and some
of the other information in a BCP format file, seem somewhat unnecessary but are needed for
use in other situations.) The second column of a mapping line is the import type. When import-
ing from a text file, this value will always be SQLCHAR regardless of what the value represents. The
third column is the prefix length. This is a rather complicated value used for BCP optimization
when copying from SQL to SQL. Fortunately, when importing text data into SQL, the prefix

■
SQL STORED PROCEDURE TESTING244
6633c09.qxd 4/3/06 2:00 PM Page 244
8.0
4
1 SQLCHAR 0 4 "," 1 caseID SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 2 "," 3 expected SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 7 "," 0 junk SQL_Latin1_General_CP1_CI_AS
4 SQLCHAR 0 3 "\r\n" 2 input SQL_Latin1_General_CP1_CI_AS
The sixth column of the mapping file controls removing an input by specifying a 0 and
controls the order in which to insert.
Because bcp.exe is a command-line program, you can run it manually or put the command
you want to execute into a simple BAT file that can be called programmatically. If you want to
use BCP from within a SQL environment, you can do so using the BULK INSERT command:
bulk insert dbTestCasesAndResults..tblTestCases
from 'C:\somewhere\newData.dat'
with (formatfile = 'C:\somewhere\newData.fmt')
A significant alternative to using BCP for importing data into a database is using the DTS
(Data Transformation Services) utility, which can be accessed through the Enterprise Manager
program. DTS is a powerful, easy-to-use service that can import and export a huge variety of
data stores to SQL. A full discussion of DTS is outside the scope of this book but knowing how
to use DTS should certainly be a part of your test automation skill set.
■
Note
In SQL Server 2005, DTS has been enhanced and renamed to SSIS (SQL Server Integration Services).
9.4 Creating a T-SQL Test Harness
Problem
You want to create a T-SQL test harness structure to test a SQL stored procedure.
Design
First, prepare the underlying database that contains the stored procedure under test by insert-

fetch next
from tCursor
into @caseID, @input, @expected
while @@fetch_status = 0
begin
exec @actual = dbEmployees.dbo.usp_StatusCode @input
if (@actual = @expected)
begin
set @resultLine = @caseID + ': Pass'
print @resultLine
end
else
begin
set @resultLine = @caseID + ': FAIL'
print @resultLine
end
fetch next
from tCursor
into @caseID, @input, @expected
end
CHAPTER 9
■
SQL STORED PROCEDURE TESTING246
6633c09.qxd 4/3/06 2:00 PM Page 246
close tCursor
deallocate tCursor
-- end script
If the stored procedure under test depends upon data, as is almost always the case, you
must populate the underlying database table(s) with rich test bed data. As discussed in the
introduction to this chapter, in a SQL testing environment, you typically have two databases:

indicate a failed fetch. So, you can loop through an entire table one row at a time like this:
CHAPTER 9
■
SQL STORED PROCEDURE TESTING 247
6633c09.qxd 4/3/06 2:00 PM Page 247