Using Inline SQL or Stored Procedures

This seems to be an area of debate among some developers, especially scripting language developers, such as ASP, PHP or that sort of language.

Whenever possible (which is most of the time) I prefer stored procedures for a number of reasons, highest on the list is security and performance. As much as I hate splitting tiers of logic, SQL and scripting logic are two areas that are very different and can be split logically. All your queries do is return recordsets of data, the code is the business logic behind what to do with that data. It seems like a logical split.

Stored procs bring with them additional security, due to the fact at the beginning of each proc you define each variable being passed to it, and the datatype of the variable, which provides additional security against SQL injection attempts and other such threats.

The stored procs are also already a compiled part of the database, they perform much quicker then inline SQL in terms of execution time to return the resulting recordset. In many small queries this improvement may not even be very visible, but when you get into large queries, joining multiple tables and returning large recordsets, the time improvement can become quite substantial.

About the only thing inline SQL has going for it is ease of maintenance. The SQL sits right there in your code in a single tier of logic, both SQL and business logic can be edited in one place and one single piece of code pushed out to get the job done. This is a weighty argument for the developer that is only concerned about their own experience. But for those concerned with the big picture, that being security of your site, user experience and server load, to me, the award goes to stored procedures with little or no debate at all.