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