Making the case for stored procedures

On a few of the mailing lists I am on for developers (primarily ones related to web development), there is a recurring topic that always gets people into rather heated debates: to use stored procedures or prepared statements?  Now, either one you choose to use, is largely dependent on two things: preference/comfort level, and the appropriate tool for the appropriate job.  This will be my attempt at defining (my opinion of) the right tool for the right job, while allowing for maximum flexibility.

Now, for those who are not aware of the difference, a prepared statement is straight SQL sent to the db for execution, but with explicitly declared parameters so as to prevent SQL injection attacks. The techniques on how to do this vary from language to language, but in ColdFusion it is through the use of the <cfqueryparam> tag, and in .NET, you use the SqlCommand object, type of text, and where you want the parameter values, you place a “?”, then add the parameters in the proper order.

Now, prepared statements are not a bad thing at all; they offer some of the same benefits of stored procedures: explicit parameterization, elevated chances of execution plan reuse, saves overhead on the db side of having to do type checking/conversion, etc.  One additional benefit of using prepared statements, it’s a heck of a lot easier to write secure dynamic SQL for complex query building.

However, stored procedures are a far better choice 99% of the time; complex dynamic SQL being the sole exception that I would consent to allowing in any database I was in charge of administering or code being checked in by a developer who worked for me. Stored procedures have several distinct advantages over prepared statements (or inline SQL in general) , and some of them become more apparent only after having built large, complex, enterprise level applications that have multiple developers and support staff working on them.

• Guaranteed execution plan reuse
• As long as you aren’t doing dynamic SQL within your stored proc and concatenating strings for execution, absolutely no chance of a SQL injection attack happening
• Performance increases for saving on permissions checks – when you submit a prepared statement for execution the database server needs to check the permissions chain on every object that that query touches, EVERY time it executes.  This is not the same as an execution plan. With a stored procedure you create the procedure and GRANT EXECUTE for that proc to the Id that your web application connects to the database as.
• Carefully controlled access to data – sometimes your data has sensitive information (like Social Security Numbers, secret question/answer information, etc) and while you have to both store that information, and maintain a level of security around it so that not everyone in the IT department has access to it, you create a stored proc, and enforce access to the data through stored procedures.  This method also gives you the ability to give partial access to data (such as a redacted SSN, instead of the full SSN)
• Modularity of code – complex logic for doing multiple operations to data in the database is easily ported from one process to another.  As an example, one application I routinely work on has 3 different methods for creating a user (new user auto-registration, administrative interface for creating them by uploading an excel file, and one that creates them via a scheduled batch job from excel files uploaded by clients) and there is some pretty complex logic for finding duplicate users across multiple clients and preventing new users from being created who exist elsewhere.   Since this piece of code is in the stored procedure, adding new sources and tools to create users is dead simple… call the stored procedure and that is that.  One argument that I heard to try and minimize the impact of this advantage “what if you had a single, well architected application written in a single language and the query was easily available from other sections of code within the application, why is there a modularity advantage for this?  Well, if you have a single language in your application, and its easily accessible everywhere else, it does lower the points this one gives you, but that was kind of my point here, it lends flexibility even if you don’t anticipate needing it now.
• Minimizing the amount of traffic sent over the wire.  Sometimes as developers we think 10Mbps/100Mbps/1000Mbps Ethernet has so much bandwidth that sending this little tiny 20 line query over the wire has no impact and won’t cause any appreciable slowdown.  Well, that is true for the most part, but wait till your application starts getting a ton of traffic… the more packets you send over the wire, the higher your collision rate on the network, and you’ll see reduced network efficiency, sad but true; also one of the most often overlooked reasons for using stored procedures.
• Wise choices in location of your business logic will greatly reduce your round trips to the database when using stored procedures.  If you are performing an operation that can be completed from a solid set of parameters from start to finish and the end result (success/failure) is the only thing you need to know, a stored procedure is what you need.  Since this was just used as an example I’ll use it again: say you are creating a user you have first name, last name, email, address, list of positions, departments, and supervisors.  If you have ALL of that logic in your middle tier and not in a stored procedure, you will have to keep going back and doing lookups for data, making more round trips to save, add, and then complete.  All of these round trips add to network overhead, time spent encoding/decoding data to send across the wire, process... locate, return…  lots of data, lots of network chatter.  This would all be eliminated with a stored procedure, you would feed one set of data to the proc, and execute it, and everything will be handled server side and the result handed back to you.
• Centralization of maintenance, changes, security, and stronger separation of tiers.
• Ease in separation in application tiers for troubleshooting.  If you write a stored proc, use it in your application, and suddenly start seeing weird results in the application, you can go, execute the proc, and eyeball the results… do they look weird? No? Then the chances are good the problem lies in your application layer, start looking there.  Many years of supporting messy applications and code taught me this lesson all too well.