Why Stored Procedures? Pros and Cons

I had to learn Stored Procedures extensively some months ago and I've found that it is somewhat quite a debated topic in the software engineering world. While most I've found favors stored procedures, there are quite many devs who are not a supporter. It can get pretty confusing deciding whether or not you should go for them. So I decided to compile a list of pros and cons based on my research to help my fellow hackers out there.

I'm not explaining what Stored Procedures are and how to write them. I will write a follow-up with what Stored Procedures are and how to write them in PL/PGSQL & PLPYTHONU. For now, you can take a look at the links I used for reference at the end of this article.

**18+ Alert: I prefer to write in a frank tone so you may find some slang as you go in. Proceed with caution.

Pros:

The pros of stored procedures circle mainly around separating database interface from code layer and prevention of SQL injection. We'll discuss these two first and some others after that.

1. Encapsulation

   Stored procedures(we'll call them sprocs from now on) let you define a separate interface for performing DB operations. It means all crud operations have to go through sprocs and as per the rules defined by them. What's the advantage here? Let's think of some use cases.
   

   1. Well, think if you have multiple applications using the same DB. Would you want codes from other applications fuck up your central DB? Would you want to risk it with your sensitive business-critical data? What if some stupid dev from another app team executes a delete/update query without anywhere clause? My point is encapsulating data manipulation on DB from business logic in code is safer as no one can fuck up your DB from code with inconsistent DB operations.
   2. Let's say you want to insert a row into a table that must pass certain criteria. For example, you want to assign an employee to a role that requires a certain score. You can implement this logic in your sprocs and all the codes from the client-side must abide by them, in short, no one can fuck around.
   3. You have some rules that have to be consistent throughout the DB, like tax calculation methods, enforce them through sprocs.
   4. I'll bring in more when I remember. 

2. Security

   Stored Procedures prevent SQL injection. SProcs are a form of parameterized query. To understand this you first have to understand query parameterization. Let me try to explain in short.
   Let's say your application has promo codes for providing discounts. When a user applies a promo code you search for it with something like the following query:
   

   SELECT * from promo_codes WHERE code="+the_code_user_entered+";

   Now the user has entered the following string for code:
   

   ABCD" OR 1=1 LIMIT 1;--

   Now your unparameterized query will be:
   

   SELECT * from promo_codes WHERE code="ABCD" OR 1=1 LIMIT 1; --";

   Which will match will all rows regardless of the correctness of the promo code and return one result and you will lose money.
   But, if you use parameterized query, your query would look something like this:
   

   SELECT * from promo_codes WHERE code=?;

   And the promo code will be passed as a parameter when you execute the query. Because SQL will compile the query and use the parameter separately as data not part of the query thus the invalid promo code entered by the user won't work.
   Ok, enough of this, this is not a SQL injection article so I'll move on.
   What sprocs do is, precompile themselves before they are called and then they use the parameters as parameters and not as a part of the query(the query is already compiled). So, you are safe.
   Having said this, you can still fuck this up, explained in the cons section.
   

3. Reduced Network Traffic

   You have multiple queries piled up to be executed one after another. Each of them would be sent over the network to DB and return results. Using a stored procedure you can combine all queries to be executed on a single procedure and call it only once from your code. Thus, network traffic is reduced and your code is faster. Also, think about when you're passing the same huge volume of params for multiple queries and how much network traffic cost is added to your application. 
   

4. Speed

   Every time you send a query to your DB to be executed, the DB compiles your query first and then executes it. But you write stored procedure once and then it is compiled and cached. Every time you call the procedure it won't recompile the queries again, it'd just use the arguments you passed with pre-compiled cached query and run it faster. 
   

5. Productivity and Maintainability

   Isolating DB operations from business logic can make your life easier. You won't have to think about how the queries are being executed on the DB, whether or not some table definitions are being changed on DB. 

   
   

Cons

1. You can still fuck up SQL injection security by writing dynamic queries in stored procedures. Think of writing a query inside the sproc using str concatenation(yes, you can do that) based on the params you pass to the procedure. Your application would still be open to SQL injection this way.
2. Debugging stored procedures is harder than your generic application code. There are tools of course but the learning curve is somewhat steep.
3. Every time your schema is changed, think of adding a new column, your stored procedure has to be updated which could hurt productivity, arguably.
4. Many find writing stored procedures not their cup of tea. So this might hurt your i/p ratio because of the learning time or adding more resources.
5. Many ORMs already take care of SQL injections.

So, that's almost all I could find useful. As for the debate, I would choose to go with Stored Procedures as they help define proper segregation of code and DB even at the cost of convenience. As for you, weigh the pros and cons for yourself and make an informed decision.

Of course, I am standing on the shoulders of the generous peeps of StackOverflow and others who have spent their time and energy spreading the knowledge they've accumulated. Kudos!

References:

1. https://stackoverflow.com/questions/1171769/when-should-i-use-stored-procedures
2. https://www.tutorialspoint.com/what-are-the-advantages-of-stored-procedures
3. https://www.sqlshack.com/sql-server-stored-procedures-for-beginners/
4. https://softwareengineering.stackexchange.com/questions/65742/stored-procedures-a-bad-practice-at-one-of-worlds-largest-it-software-consulting/66084#66084
5. https://www.geeksforgeeks.org/advantages-and-disadvantages-of-using-stored-procedures-sql/
6. https://searchoracle.techtarget.com/definition/stored-procedure
7. https://security.stackexchange.com/questions/68701/how-does-stored-procedure-prevents-sql-injection
8. Google