Why avoid stored procedures? Here are some reasons:
1. Stored procedures are generally written in some proprietary language such as PL/SQL and even when not (for example,
Java stored procedures) are likely to use proprietary API's. That locks your apps into one vendor's database. If Amazon can move their business to a different database vendor, so might yours.
2. Stored procedures risk fracturing business logic. If part of the business logic is in the app and part in the database (stored procedure), it can be a "treasure hunt" to find what's being done where when maintaining systems.
3. Stored procedures run on the database server machine. Typically a database server will have many application client machines, which allows distributing the workload. But if expensive logic can only run on the database server, then it can over-burden the database server and potentially slow down all of the client machines as well.
4. Not all databases have version control. With application code you can do version control with a system such as
git or Subversion.
So why use stored procedures at all?
1. If several different applications need complex logic then it's better to maintain that code in one place and not have to update it in multiple places. It's still better to share a code library, but code libraries are limited in that typically they are bound to a particular programming language, which may be a problem when apps aren't all in the same language.
2. Sometimes you can get much better performance if you do processing on the database server instead of sending tons of data to the application and having the application send tons of data back to the server. However, always keep item 3 from the "why not" section in mind!