I'll let you in to a little secret: I love Stored Procedures.  Don't tell anyone though, will you, I wouldn't want that getting out.

So much of what we do these days deals with data, and lots of it.  And often, that means relational databases.  A lot of people like to drop simply SQL queries directly in to the [PHP, Java etc etc] code they're writing and then further manipulate the data that comes out in [PHP, Java etc etc] code, but that is ignoring all that a decent DBMS can do:  In a Stored Procedure you can guard all that data properly, access it securely in a handful of clearly defined ways and manipulate it in so many clever ways before delivering just the results that are needed to your app.

So what makes them so incredibly useful?

First off, they are secure.  Sure, you can do insecure things with Stored Procs if you really try, but generally they are easier to secure than ad-hoc queries.  This security comes from two features:

  1. The variables passed in are strongly typed.  One can only pass an int in to an int argument, string in to a varchar, etc.  MSSQL will complain bitterly if you get this wrong, and MySQL will implicitly cast to the right type.  Unless you are doing something with Dynamic SQL within the procedure, the arguments are passed in to any internal queries by value, so no worries with SQL injection here.
  2. If every data manipulation your program performs is via a Stored Procedure, then the user with which your main program (The PHP, Java, Python, etc etc) connects to your database needs only have "execute" access.  So any other kinds of SQL injection which might attempt to select, update or drop tables simply will not work.

They are fast to execute.  This is actually quite a contentious issue, as it is implementation-specific.  But the theory is that all the work the query compiler and query analyser do to figure out how best to process the SQL you've written only needs to be done the first time a procedure is run.  This is only saving milliseconds, but it could make quite a difference on a very loaded web server running the same SQL many times a second.

They are fast at dealing with large data sets.  This is what a DBMS is for: crunching large amounts of data.  If you extract large amounts of data and try to crunch it in an interpreted language, it will be slow.  A Stored Procedure allows you to crunch and re-crunch data, passing it through many stages, until just the small amount of data you wish to render to the user is returned.

They let you test your data manipulations away from the web.  This is a specific bonus in my area of programming.  If you can black-box your data manipulation and retrieval in to layers of Stored Procedures, you can test the data input and output before you write the PHP that renders it.  Once you get in to the habit of encapsulating your data in this way, it becomes much easier to later manipulate the data logic without breaking the rendering of that data:  you have completely split data manipulation from user interface rendering.

They let you validate close to the inserts and updates, easily doing complex multiple inserts across many tables quickly and neatly with the minimum of input data, returning user defined errors easily if that input data is somehow wrong or if the user does not have permission to perform a particular operation.

They help you maintain integrity by providing well-defined ways of accessing and updating your database.

They let you create complex models of your data.  An SQL server is not just a program that orders and contains your data, it is also an advanced execution and data mining environment.  It is specially designed to let you crunch big data, and there is very little that isn't possible in data manipulation that can't be done within the confines of a Stored Procedure.  Performing all calculations, manipulations and condensing of your data before it leaves the server is very fast, and leaves your main program free to simply render the results on screen.  You are almost never showing the user a large results set on screen, so why should you have your DBMS transfer a large results set back to your UI code?

By using different complex models of your data, you get an added benefit of being able to very easily create different "renderers".  By segregating all data processing to the database, you can use this single, rich, complex datasource to fuel a PC layout, a mobile layout, CSV/report generation and JSON / XML web-services using very generic code and no extra, costly transformations.

If this has made you curious enough to see what you can do with Stored Procedures, I'd recommend chapters 13 and 19 of the MySQL Reference Guide: http://dev.mysql.com/doc/refman/5.1/en/index.html

In future articles, I'll show you how to fully integrate Stored Procedures in to PHP, allowing you to create an easily callable database API, complete with logging, exception handling and low level authentication.

Enjoy.

...Click for More
Article
IT
Programming
SQL
Stored Procedures