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
Understanding the nuances between different types of test efforts can be a challenge. In this expert response, Karen Johnson explains what is meant ... Continue Reading
When testing user interface (UI) components of an application that uses a right to left language (RTL), there are special considerations. Software ... Continue Reading