ermissions are restricted in most environments. Even in development environments, only members of the sysadmin and serveradmin fixed server roles can execute the sp_sdidebug and sp_addmessage extended procedures to grant use of SQL Server’s debugger and custom error messages, leaving the developer to their own devices for error handling during development.

Complicating matters is the fact that error trapping is primitive in SQL Server development because some errors can break procedures prematurely. This means you can’t find out what’s wrong because the call to the error trap in your code never gets executed! So if you are running in Query Analyzer, an error can occur and you may never know what caused it because your procedure stopped before it could be reported. Errors are sent to the client that called them, which leaves Query Analyzer out because it doesn’t qualify as a proper client.

I confronted these limitations during a recent project. I had to build functionality into the code to determine what was going wrong with a stored procedure under development.

How can I test my stored procedure code for the different combinations of input parameters so that it will behave as expected? How do I work around the absence of permissions to execute SQL Server Debugger or the ability to use Raiserror to debug my code?



You have two alternatives. One is to create a stored procedure that executes the procedure under development. It will call the procedure with every combination of input parameters possible. The second is to use the command window as a client, and call the procedures from there. This re-routes the server’s error messages to the command line, where you can trap them to a file and read them outside Query Analyzer.

Spell Out Your Procedure’s Purpose
A stored procedure works best when doing only one thing. However, some shops are under pressure to localize the logic underlying data modifications. This centralizes code maintenance by putting the logic in one procedure instead of three or four (e.g., procedures for insert, update, and delete). Unfortunately, combining these actions makes the underlying procedure exponentially more complicated. Instead of doing only one thing, the procedure is pressed into general service, doing three or four things.

Your first line of defense in debugging this type of general-purpose stored procedure is a good header that enables you to document its purpose(s). You can over-explain things in this documentation, since how things happen should be self-evident to the reader, but the reason why an approach was taken may not be immediately evident. So the procedure’s intended purpose is where you should spend your efforts when filling out your header (see Figure 1).

Figure 1: Sample Header for Your Stored Procedures

Once the header is set up and maintained, you have a running history of what is happening in the code.

Determine the Flow of Control
By branching based on the parameters sent to the procedure, you delegate control to the relevant sections to do the DML required. Assume you have a procedure that has one parameter: @Action, which could be an Insert, Update, or Delete parameter. To test the value of @Action and perform accordingly, you need a structure something like this:

IF @Action = XXXXXXBEGINYour code hereEND

You should get this fleshed out by pseudo-coding your intention first and then creating the logic to accomplish what you intend. Since most errors are logic errors, getting this down in stub form will assure that the code is at least executing the flow of control correctly. This falls under the heading of “creating the algorithm first, then writing the code later.” It’s a good (but sometimes overlooked) practice to say what you want your code to do before you write it! The following code first stubs the flow of control:

CREATE    PROCEDURE dbo.usp_TestFacilityRoster	@InAction 	VARCHAR(15),	@EntryMonth INT,	@EntryYear 	INT,	@FileType 	CHAR(1)AS	IF @InAction ='NEWREINS' or @InAction = 'NEWREINSSNP'		BEGIN			--do stuff for new members		END	IF @InAction = 'DISENROL' OR @InAction = 'EXISTING'		BEGIN			--do common stuff for existing or disenrolled members			IF @InAction = 'DISENROL'				BEGIN				--specific for disenroll			ELSE			IF @InAction = 'EXISTING'				BEGIN				--specific for existingEND

Now, you’ve established the backbone for the procedure.

Managing Errors Without Raiserror
Working from the command line is as close as you can get to imitating the facilities available from Raiserror. You can get most of Raiserror’s benefit (error messages sent to the client) by executing your stored procedure from the command line by invoking OSQL. It will let you create a log file that can catch the server error messages. Other, more elegant approaches are possible using WSH or VB, but do you have the time to build an application to test an application? I didn’t, so I split the difference by stubbing an error-trapping device at the end of the procedure. I then could get some insight into what went wrong, assuming that what did go wrong allowed the procedure to actually complete. If the error caused the procedure to end abnormally, I was out of luck because the error-trapping code would never be reached if the batch broke.

The following code is an error trap to test for parameter errors:

BEGIN--PARAMETER ERRORS--------------------------------------------------------	DECLARE @Errcode Int 	--sql error codes for transaction management	DECLARE @InAction_Error INT, --custom error codes		@EntryMonth_Error 	INT, 		@EntryYear_Error 	INT, 		@FileType_Error 	INT,		@NewReins_Error 	INT,		@Disenroll_Error 	INT,		@Existing_Error         INT--ERROR CODES-------------------------------------------------------------	IF @InAction IS NULL 		BEGIN			SET @InAction_Error 	= -10			GOTO ERRORH		END	IF @EntryMonth 	IS NULL 		BEGIN			SET @EntryMonth_Error  	= -20			GOTO ERRORH		END	IF @EntryYear 	IS NULL 		BEGIN			SET @EntryYear_Error 	= -30			GOTO ERRORH		END	IF @FileType 	IS NULL 		BEGIN			SET @FileType_Error  	= -40			GOTO ERRORH		END

The branching provided from this code put the procedure into a custom messages section of the code, where the message was displayed in the development environment. A custom message gives some inkling to the user of what was wrong with the parameter, and how to fix it so the procedure could run.

The following code represents some additional overhead in that you need to install the trapping above and create the GOTO Label section given below, but without it, you are in the dark as to what went wrong:

ERRORH:IF @InAction_Error  <> 0 BEGIN	IF @InAction_Error = -10	BEGIN		Print 'ERROR -10 - Please set the first parameter to '+ char(10) +		      '''NEWREINS'' for New Reinstate, '		   + char(10) +		      '''NEWREINSSNP'' for New Reinstate with SNP, '+ char(10) +		      '''DISENROL'' for Disenroll or '			   + char(10) +		      '''EXISTING'' for Existing'	ENDENDIF @EntryMonth_Error<> 0 BEGIN	IF @EntryMonth_Error = -20	BEGIN		Print 'ERROR -20 - Please set the second parameter to '+ char(10) +		'an integer that stands for the Entry Month'	ENDENDIF @EntryYear_Error <> 0 BEGIN	IF @EntryYear_Error = -30	BEGIN		Print 'ERROR -30 - Please set the third parameter to '+ char(10) + 		      'an integer that stands for the Entry Year'	ENDENDIF @FileType_Error <> 0 BEGIN	IF @FileType_Error = -40	BEGIN		Print 'ERROR -40 - Please set the fourth parameter to '+ char(10) + 		      '''A'', ''S'' or ''P'' for the File Type you are using  '	ENDENDGO

The native messages are no help, so this code lets you help yourself. It branches based on which parameter was insufficient so that the user (you) knows what to supply to the procedure on the next run.

In an effort to nip bugs in the bud, you should subject your code to boundary conditions, things that you might not have fully considered during development. For example, since the underlying tables might use characters instead of datetime datatypes, the incoming parameters are of type INT. This means you can’t use date logic to test them.

For dates, in addition to the anticipated date parts of month (1-12) and year (1999-2004), use month zero, month 13, and month 100. To test your year handling, use year 0, 1,1900 and 10000. In addition to having an @@Error trap for the error codes returned by the database, you can get in front of the error by testing the values of the input parameters before execution. This way, you can handle the consequences of invidious input before it does any damage. Another benefit is that this approach informs the client of an appropriate list of values to get the procedure to work as anticipated.

QA Testing vs. Programmer Testing
Quality Assurance personnel test differently than programmers do, because the two groups have different goals. The programmer wants their program to work, while the QA staff wants it to break. Therefore, QA testing is often rigorous and comprehensive, subjecting code to unexpected inputs that represent extreme values and unanticipated stress consisting of loads that are in excess of what will be encountered in production. In addition, QA’s regression testing gauges the effects that integrating the new code will have on existing code. QA is interested in unit testing, regression testing, and user-acceptance testing. Development is interested in getting it to work, so testing that is conducted by the programmer who authored the program is often less comprehensive–more gentle, if you will. Because of this more nurturing attitude, the phrase “programmer tested” often amounts to “Gee, it worked on Tuesday.”

Stored procedure code is rather inflexible. T-SQL programming doesn’t include many of the enhancements available to other programmers. Because the call and return features of TSQL are limited, you need to have your code run once through. The execution path is not as commodious or robust as the ones application programming languages offer.

Test Stored Procedures for Any and Every Thing
You don’t (yet) have the benefits of try/catch, or calls and retries. For example, you have no choice but to rely on application code to retry after a component reports a 1205 error. For another, if a transaction is nested within another transaction and something untoward happens in the lower level that forces the transaction to roll back, the rollback percolates all the way back up the chain to the transaction wrapping the failed one–so the work preceding the failure is undone.

This inconvenience is a fact of life for T-SQL. As database programmers, our first concern is data integrity. So the failure of a lower-level transaction demands undoing the work preceding it (notwithstanding the use of commits or check-pointing to save intermediate steps along the way).

Let the System Do the Work
By creating a parameter mill, you can feed a stored procedure a collection of parameters that represent the universe of things it can accept, enabling you to test the scope of conditions anticipated by your code. You can automate the boundary conditions with a table and procedure to do the drudgery for you.

Create a test plan table with the following code:

create table p1 (param1 char(8))create table p2 (param2 int)create table p3 (param3 int)create table p4 (param4 char(1))create table testplan (param1 char(8), param2 int, param3 int, param4 char(1))insert p1 values('NEWREINS')insert p1 values('DISENROL')insert p1 values('EXISTING')insert p2 values(1)insert p2 values(2)insert p2 values(3)insert p2 values(4)insert p2 values(5)insert p2 values(6)insert p2 values(7)insert p2 values(8)insert p2 values(9)insert p2 values(10)insert p2 values(11)insert p2 values(12)insert p3 values(1999)insert p3 values(2000)insert p3 values(2001)insert p3 values(2002)insert p3 values(2003)insert p3 values(2004)insert p4 values('A')insert p4 values('S')insert p4 values('P')insert testplan select * from p1,p2,p3,p4

This will create a Cartesian Product (cross join) of all the combinations of the parameters your procedure can accept.

During development, you try to mimic the call to your procedure by writing something like this near the top of the code:

/* TEST BLOCK set @InAction   ='NEWREINS' set @entrymonth = 7set @entryyear  = 2003*/

Instead, you can create a string to execute the stored procedure that leaves placeholders for your procedure’s parameters:

select 'Execute dbo.usp_TestFacilityRoster('+''''+ param1 + ''''+','+cast(param2 as char(2)) +','++cast(param3 as char(4))+','+''''+param4+''''+')'from testplan

This code doesn’t execute it though. You need to use dynamic SQL to execute the string. So the above code can be wrapped in a cursor like this:

set nocount on declare  @param1 varchar(25)    ,@param2 varchar(2)    ,@param3 varchar(4)    ,@param4 varchar(25)declare C1 Cursor forselect param1, param2, param3, param4     from testplan (nolock) open C1 fetch next from C1    into @param1, @param2, @param3, @param4while @@fetch_status = 0begin        exec dbo.usp_TestFacilityRoster, @param1, @param2, @param3,@param4fetch next from C1    into @param1, @param2, @param3, @param4endclose C1 deallocate C1 -- this statement builds the string from the table Update TestplanSet @sqlstring2 = @sqlstring2 + cast(param1 as char(8)) + ', '+cast(param2 as char(2)) + ', ' + cast(param3 as char(4))+ ', ' +cast(param4 as char(1)) print @sqlstring2set @sqlstring = @sqlstring + @sqlstring2execute sp_executesql @sqlstring

You also can use this approach from the command line and imitate the testing procedure outlined above by using the FOR IN DO construct in a batch file, or in better flow of control language from WSH so that you can make repeated calls to your procedure with the different parameter sets. However, the approach given here lets you work from within Query Analyzer without having to resort to the command window or another language to get the job done.

These ideas were necessary for me to circumvent the absence of permissions to run the SQL Debugger or to install my own error messages so that I could use ‘raise error’ in my assignment. This Solution has provided a few ideas for getting around similar limitations in your own environment, and they lets you test your code automatically to have a higher degree of confidence in the correctness of your own solutions.