Understanding Stored Procedure

The reasons to use Stored Procedure: simplicity, security, performance

1. To simplify complex operations by encapsulating processes into a single easy-to-use unit

2. To ensure data consistency by not requiring that a series of steps be treated over and over. If all developers and applications use the same stored procedure, then the same code will be used by all.

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

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

5. To enhance security. Restricting access to underlying data via stores procedures reduces the chance of data corruption(unintentional or otherwise)

6. Because stored procedures are usually stored in a compiled form, the database system has to do less work to process the command. This results in improved performance.

The downside of using stored procedure:

1. Stored procedures syntax varies between different DBMS

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