Arguments for and against using stored procedures

Frans Bouma states some arguments against using stored procedures, preferring to generate SQL dynamically in an application.

Coming to databases from an OO background, I'd suggest that the database should be considered an "object" with a defined interface. The implementation should be separate from the interface, and coupling between objects should be reduced as far as possible, and I’d use SP's mainly for this reason.

Ed Tittel suggests that technologies should be evaluated by considering performance, availability, security, scalability, maintainability, accessibility, deployability and extensibility. I think considering these criteria, SP's have advantages.

Performance – There may be some small improvements with SP's, as these can be pre-compiled. However, newer versions of SQL server maintain a cache of execution plans of other queries, so any gains are likely to be small. You certainly don’t lose anything with SP's however.

If you decide your application is too slow, and want to cache data to optimise things, you can do this in the database, and don't need to change your application.

Availability – N/A

Scalability – Separating the implementation from the interface allows the implementation to change. If, for example, you wanted to partition your data so it appeared across several servers, you could change your tables, change your stored procedures to use the new tables, but if you keep the interface to the stored procedures the same, you will not need to change your applications.

However, the more proprietary database technology you use, the more you are locked in to Microsoft technologies. Microsoft have some impressive performance figures for SQL Server on large-scale environments. However, sometimes when scaling a database up, you might want to move to something else, and using SP’s would make this change take longer.

Security – If the underlying tables are hidden, the risk that they can be accidentally or maliciously changed is reduced. Access to stored procedures can be granted to individuals or application roles who need it. However, they should not be seen as a technique to magically prevent SQL injection attacks.

Maintainability – It is probably easier to make small changes to an application that uses SQL to query the database directly. However, it is harder to ensure these changes will be correct and reliable. Using stored procedures allows Test Driven Development and refactoring to be used (see my earlier post here ).

However, many developers do not understand how stored procedures work, and may develop them sub-optimally. You need to consider who is available to change these if they need to be maintained and what their skills and aspirations are.

Accessibility – N/A

Deployability – If the database is a self contained object, with a limited number of methods (stored procedures), it is less risky to redeploy this object in isolation. The more ways that code can access the database, the more chance that something could go wrong, and the easier it will be to correct any problems.

Extensibility – Using SP's allows your application to be extended more easily without damaging existing functionality. If your application needs different information from a database, you can easily write another SP. You can run unit tests over your existing procedures to make sure that anything you have written does not break existing code.

If you want to write another application that uses the same data, you can reuse the SP’s, and any optimisations you have made to them.

Douglas Reilly has also looked at this here and has concluded that SP’s improve maintainability and performance (to a smaller extent).

I would use SP's at all times except where the following apply:
1) The database technology might change.
2) Developers and maintenance personnel do not understand SP's and are not able or willing to learn about them.
3) You are developing a very small project.
4) The project is a one-off development and unlikely to need maintenance.