Browse DevX
Sign up for e-mail newsletters from DevX


Get Your Stored Procedures Ready for Anything-4 : Page 4




Building the Right Environment to Support AI, Machine Learning and Deep Learning

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 = 7 set @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 for select param1, param2, param3, param4 from testplan (nolock) open C1 fetch next from C1 into @param1, @param2, @param3, @param4 while @@fetch_status = 0 begin exec dbo.usp_TestFacilityRoster, @param1, @param2, @param3, @param4 fetch next from C1 into @param1, @param2, @param3, @param4 end close C1 deallocate C1 -- this statement builds the string from the table Update Testplan Set @sqlstring2 = @sqlstring2 + cast(param1 as char(8)) + ', ' +cast(param2 as char(2)) + ', ' + cast(param3 as char(4))+ ', ' +cast(param4 as char(1)) print @sqlstring2 set @sqlstring = @sqlstring + @sqlstring2 execute 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.

Drew Georgopulos is the database architect for GoAmerica Communications. He moonlights as an adjunct professor of computer science at Marymount College of Fordham University, teaching systems analysis and relational database design. .
Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date