Problem solve Get help with specific problems with your technologies, process and projects.

Software database testing stored procedures

Database testing can refer to any backend or data-related testing such as data migrations and data integrity. In this expert response, Karen Johnson talks about two practices that she's used for testing stored procedures (SP): code reviews and building data sets to test the functionality and performance of a stored procedure.

I am a testing engineer. I need to know about database testing i.e., how to test functions, stored procedures and how to test the performance of stored procedures.
Database testing seems to be a loose term used to refer to any backend or data-related testing when in fact there are many different types of backend data testing-- such as data migrations and data integrity.

The two practices I have participated in and have seen used for testing stored procedures (SP) are:


  1. 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.
  2. 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 Software Testing Tools and Frameworks

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.