Wednesday, June 28, 2006

Stored procedures or inline SQL

Using stored procedure versus using inline SQL has been a debate for long in the software development community. There are good articles on the subject, including:

Generally the debate is about best practice, security, performance and maintainability with two camps:

  • Rapid Application Developers prefer to rely on tools (RAD, ORM) that generate code, which all use inline SQL statements. This camp will claim that their tools enforce best practices, prevent SQL injection attacks and ensure maintainability while SQL execution plan caching makes performance equivalent to stored procedures.
  • Developers who write their own business layer and data access layer code without support from such tools should opt for Stored Procedures which will make their application generally more secure, maintainable and performing better. I am on this side. Also note that having all your SQL code in stored procedures makes it easier to have it audited and reviewed by a database expert.

No comments: