I happen to see a query in Google group on software Testing regarding testing of stored procedures ( as a part of Database testing). Here are my views on this topic
In one way Stored Proc (SP) Testing is like a testing an API. So apply all the rules that you would apply for API - to SPs also. You can design test cases treating Stored Proc as black box - design all possible combinations of valid and invalid inputs and observer outputs. Be sensitive about the fact that when a SP is tested like an API – you will supply certain inputs which are otherwise not likely to be fed to it if used by a middleware component. So developer may reject a bug related to SP saying that SP will be never be called with such set of strange of inputs – client/middleware layer will filter all bad or unlikely inputs.
For structural Testing (white box) following things come to my mind...
1. You might want to consider measuring Cyclomatic complexity of the code (of loops in it). I believe there are some tools that measure this. Here are few links related to CC measurement. By the way this is also referred as "Code complexity". Higher the code complexity - higher the testing effort required to validate it.
http://www.sei.cmu.edu/str/descriptions/cyclomatic_body.html
http://www.linuxjournal.com/article/8035
2. Databases products like MS SQL server (oracle - not sure) provide monitoring / profiling tools to assist in the time taken for SP execution and other runtime parameters. This will give good idea about SP's Runtime performance. I used MS SQL server Profiler tool to monitor SP execution.
3. You could test SP for security/access control.
Shrini