Let's look at the possibilities:
Reduce network traffic
improve database performance
create scheduled tasks
reduce risk
create processing routines
The main thrust of Stored Procedures is to encapsulate behavior along with the database when, for whatever reason, it is not desirable that these are modeled on the client application (s). This is the main (but not the only) reason for using Stored Procedures .
Motives 3 and 4 are derived from 5. Motives 1 and 2, although valid, are optimizations that should only be created as special cases to solve specific problems.
A real example of using Stored Procedure would be to create some CRUD procedure, especially if you wrap multiple tables and place it directly in the database. The advantage of doing this is that data access is encapsulated and client complexity is reduced. In case of item 3, you can even eliminate the need for a client program.
The downside is that changes to requirements and business rules require changes to the database, which tend to be more costly and difficult than changes in client applications.
Another advantage is that if you have multiple client programs, using Stored Procedures , you centralize the business rules all in one place. However, this advantage is considered to be surpassed today. You can use a web service (SOAP, REST or something else) that has exclusive access to the database, and all other client applications would only access it through this web service. In this way, the web service would be responsible for centralizing the business rules, not the database anymore.
Cases 1 and 2 can also be advantageous in those circumstances where you make a giant% s with multiple tables and the client application uses this to group, totalize, or do some other processing. You can reduce network traffic between the database server and the client application, and thereby improve performance if these operations are performed directly by the DBMS using Stored Procedures . >
