A stored procedure(SP) is a set of Structured Query Language (SQL) statements with a given name stored in RDBMS, so it can be reused and shared by multiple programs.
Performance
First SP in some databases system are compiled once and are in always executable form making calls faster since they can be cached. Therefore, increasing performance.
From my experience in using SP, I have been able to group multiple SQL commands and queries. This reduces network traffic and chaining of IFS on the code side. SP makes use of transactions e.g. using PL and T-SQL.
Security
SP enhances security since the user has to pass some values to the database without knowing the underlining tables, views, and other information. The Developer calls the methods. This makes use of abstraction. The business logic is abstracted.
Maintenability
SP enhances the maintainability of the System. Some conditions can be changed without necessarily changing the frontend code. Let's say the VAT tax reduces from 16% to 12%. This change can be made in the database without recompiling a POS system developed in 20 supermarkets. Some DB ensures changes in SP are transferred to all other slave databases.
SP ensures a central point for all business logic. No scattering of the business logic all over.
Downside
one negative side of SP is that it leads to vendor locking eg T-SQL and PL/SQL. When migrating to a new DB you need to write some of them from scratch.
Privilege Abuse
How do we ensure security and authorization? users who have DB-admin roles may abuse their roles and execute some SP maliciously. Ensuring data ACID properties becomes a challenge to an organization.
How best can we avoid database degradation?
Where applicable?
I would recommend using SP when:
- the business logic is ever-changing
- doing complex data processing like in a warehouse
Latest comments (1)
RE: Maintainabiity --- Let's say the VAT tax reduces from 16% to 12%. This change can be made in the database without recompiling a POS system developed in 20 supermarkets. Some DB ensures changes in SP are transferred to all other slave databases.
I disagree. This change should be made in the DATA. Not in the stored procedure. Any value that can change should be defined in a table. Maintainability is more the effect of good database design, rather than using stored procedures. Design your database so that business rules / logic can be defined in the tables