I find the option of stored procedures to be very appealing for a variety of purposes. Among other things, I've seen plenty of extremely complex SQL queries that only change by one parameter, which means that they could be wrapped up into a call that would require significantly less PHP markup and significantly less network traffic between the web and database servers. Add multi-query procedures, and the procedures would be even better.
In the end, I decided not to pursue stored procedures right now because:
- There is no Drupal-centric way to create or manage them
- A stored procedure would be unable to leverage the Drupal hook techniques as intended, especially db_rewrite_sql
- Beyond network bandwidth savings, MySQL procedures may not have the performance boost that might be expected due to the scope of availability of compiled functions
- MySQL read replicas (and therefore RDS read replicas) are unreliable with most non-deterministic functions
- On top of all that bad news, it was going to take tweaking of permissions to even get to where I could test
Although procedures might be an amazingly effective solution, the research was all so pessimistic that I've decided not to commence with testing at this point. It will remain at the front of my mind -- hopefully a use case will justify further research, development and testing.