-
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.