There are mainly two points about stored procedure testing: one is business logic, or realization function, and the other is the performance, that is how to find the optimal SQL statement.
Business Logic
When putting some stored procedures in the database, you need to test whether they have realized their functions without excessive functions, and whether you get the data you want without the useless data. The data is to be transmitted after acquiring it, so if there is unwanted data, though can be deleted when acquiring it, but it will give heavy stress to the server. As time goes by, errors can be unavoidable. In terms of a series of stored procedures, you need to figure out whether they accomplish what you need, and run the process smoothly.
In terms of testing of business logic, you can execute the statement in the database, and do a manual testing by sending in some parameters. You must ask whether automated testing can be conducted. Of course, there are some techniques. Simply, I achieved it by C language. I called stored procedure by ADO.NET, sending in some parameters and executing the stored procedure. Just by click a button, I would run all the stored procedure. In the process, it is hard to collect data, for the data has to be able to be used all the time.
To make it smarter or you don’t have to click a button and the stored procedure can be executed, of course we should set it to automatic operation when starting the PC. There is a tool named Nunit that can be used. You just need to write the stored procedure codes, and upload it after compiling, and it will run all the codes after clicking the execution button, and the ones impassable will be colored. Nunit can be used with other plug-ins like Nant, Nunit2Report, nantcontrib. An automated testing platform can be established with all these plug-ins combined together.
The Performance of Stored Procedure
Stored procedure comes from modified SQL statement. Speaking of the performance, it is also the performance of SQL statement. The performance of an SQL statement can be measured in many ways. Experienced guys will know the performance of most SQL statements. Thus, the first way is to observe the statement. At present, some software allows you to check the time spent in operating SQL statement, and you can figure out the performance of SQL statement. Under the same circumstance, it is obvious that the less time, the higher the performance.
There are several ways to check the time. PL/sq will show you the time of operating SQL statement. You can also call stored process by ado to check the time, which is very easy.