Now that you know what stored procedures are, why use them? There are lots of reasons, but here are the primary ones:
To simplify complex operations (as seen in the previous example) by encapsulating processes into a single easy-to-use unit.
To ensure data consistency by not requiring that a series of steps be created over and over. If all developers and applications use the same stored procedure, then the same code will be used by all.
An extension of this is to prevent errors. The more steps that need to be performed, the more likely it is that errors will be introduced. Preventing errors ensures data consistency.
To simplify change management. If tables, column names, or business logic (or just about anything) changes, then only the stored procedure code needs to be updated, and no one else will need even to be aware that changes were made.
An extension of this is security. Restricting access to underlying data via stored procedures reduces the chance of data corruption (unintentional or otherwise).
Because stored procedures are usually stored in a compiled form, the DBMS has to do less work to process the command. This results in improved performance.
There are SQL language elements and features that are available only within single requests. Stored procedures can use these to write code that is more powerful and flexible.
In other words, there are three primary benefits—simplicity, security, and performance. Obviously all are extremely important. Before you run off to turn all your SQL code into stored procedures, here's the downside:
Stored procedure syntax varies dramatically from one DBMS to the next. In fact, it is close to impossible to write truly portable stored procedures. Having said that, how the stored procedures call themselves (their names and how data is passed to them) can be kept relatively portable so that if you need to change to another DBMS at least your client application code may not need changing.
Stored procedures tend to be more complex to write than basic SQL statements, and writing them requires a greater degree of skill and experience. As a result, many database administrators restrict stored procedure creation rights as a security measure (primarily due to the previous bullet item).
Nonetheless, stored procedures are very useful and should be used. In fact, most DBMSs come with all sorts of stored procedures that are used for database and table management. Refer to your DBMS documentation for more information on these.
