This is more of an architecture question. I once worked at a financial company that had a web application.
Front end javascript.
Middle layer was WebAPI to access the backend SQL Server.
Back End SQL Server database.
The company put all its business logic in stored procedures. After talking with other companies, I began to hear that most put their business logic in the middle layer.
I once asked someone at the financial company why was there such a big use of stored procs but didn't get a good answer. Recently, at a job interview, they asked what the advantages and disadvantages were of putting all the business logic in the database vs the middle layer. I didn't have a good answer.
Can anyone provide some thoughts on why this is or is not a good idea?
Best Answer
A) Scale The middle tier can be scaled easily - hence the web farm concept. Scaling out the DB tier is much more difficult. While some products can do this it is not yet trivial and mainstream.
B) Cost Typically web servers are common-or-garden boxes. DB servers, however, tend to be larger, more complex and more resilient. This all translates to "expensive." A recent employer estimated a CPU tick on the DB was ten times more expensive than one on an application server.
C) Reuse Logic embodied in a stored procedure cannot simply be linked into, say, a stand-alone mobile app. Changing the SP affects every application which uses that DB, whether that application is ready for the change or not.
D) Reuse Logic embodied in a stored procedure is common to all applications which use the DB. Programmers cannot side-step the rules at a whim. Changing the SP affects every application which uses that DB, ensuring consistency across the enterprise.
E) Tooling There are more, and more sophisticated, languages, tools and techniques available for development in the application tier than there are in the database (from comments, with thanks).
F) Network traffic Typically a business function will require many reads and / or writes. Often the output of one statement will be the input to a following statement. If SQL statements are held in the application each will require a network round-trip to the server. If the SQL is held in a stored procedure there will be a single network trip, sending the parameters in and receiving only the final result back; the network cost of the intermediate results is avoided.