The two practices I have participated in and have seen used for testing stored procedures (SP) are:
- Conduct code reviews of the stored procedures-- SPs are in fact as powerful and manipulative as code, so both code reviews and version control are practices to consider implementing.
- Build data sets to both test the stored procedures as well as test the performance of a stored procedure. Let me explain more specifically:
To test a stored procedure functionally, you can build one or more sets of data that test both a positive as well as negative aspect of the procedure. A positive test to prove the SP works against the specific data set and handles the data as expected as well as a negative set of data-- it might be data that falls outside of the SP to ensure that the SP does not impact data it is not expected to. Also you can challenge the SP-- creating data sets that push boundary condition or fall on the edge of a business rule to see how gracefully the SP handles those types of challenges.
To test the performance of a SP, you could follow a similar process building larger data sets. You could also isolate sections of the SPs and run those separately to see how specific SQL statements execute in terms of performance.
Dig Deeper on Topics Archive
Related Q&A from Karen N. Johnson
User acceptance testing and system integration testing differ in one key way: the person who does the testing. Learn when to apply UAT vs. SIT. Continue Reading
Initiating test automation on your project team may seem challenging, or even overwhelming. Fortunately, expert Karen Johnson has been through this ... Continue Reading
New mobile phone models enter the market all the time, and it seems daunting to perform application testing on the various devices available. Expert ... Continue Reading