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.
- Make the stored procedure handle one object/table only and not multiple objects or tables.
- Do not commit open transactions inside the stored procedures.
- Do not do business logic in stored procedures.
- If they are straight CRUD stored procedures, see if you can generate the stored procedure code using some metadata?
- Make sure creation and execution of the stored procedures is part of your Continuous Integration build and developer build.
- 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
- Develop against the production stack database.
- Make sure exceptions thrown by the database are passed back to the application.