Considerations when using stored procedures for doing CRUD.

Posted by Pramod Sadalage on Tuesday, February 3, 2009

Some environments like to have access to the database tables routed via stored procedures. Instead of using Create/Read/Update/Delete (CRUD) with DML, stored procedures are invoked with the parameters to perform the required operation. I’m not arguing about the benefits/pitfalls of this approach, if you have to do stored procedures, here are some things to look at.

  1. Make the stored procedure handle one object/table only and not multiple objects or tables.
  2. Do not commit open transactions inside the stored procedures.
  3. Do not do business logic in stored procedures.
  4. If they are straight CRUD stored procedures, see if you can generate the stored procedure code using some metadata?
  5. Make sure creation and execution of the stored procedures is part of your Continuous Integration build and developer build.
  6. Make sure stored procedures (or the metadata used to generate them) is under Version Control, have seen many problems when the stored procedure version does not match application code version
  7. Develop against the production stack database.
  8. Make sure exceptions thrown by the database are passed back to the application.