There's a few things to consider here, which I'll try to explain in enough detail to help you understand the problem space. Personally, for RDMS', I prefer using stored procedures, reasons for which I'll go into in a moment.
In any discrete application layer, there is a need to ensure that the boundary of that layer has sufficient controls in place to prevent dirty data from entering the layer, which could then cause corruption. This is the case regardless of whether the layer is an API exposed publicly, an internal API (e.g. front controller negotiating with services), or calls to an external layer (e.g. communicating with the data store). In DDD this is called the Anti-Corruption Layer, in OOP it goes by a few names (Design By Contract, Assertions, Pre-/Post-Conditions), and it can take as simple a form as strongly typing input and output variables. In any case, the purpose of this layer is to ensure that the layer can consistently and predictably enforce communication standards for input and output operations.
What does this have to do with stored procedures vs. ad hoc SQL?
Allowing ad hoc SQL to be executed against the database is a form of cowboy coding, in that the SQL query itself is not governed, the only measure of it's validity is whether or not it executes successfully, and the only safety net in place is wisdom (of the application) by the developer and/or code reviewer. Combine this with a latent notion that no business logic should go into the database (i.e. no constraints or triggers to ensure data structure remain intact), and you begin introducing risks to data integrity. An example of this is an object being saved to database that needs to be updated/inserted in multiple tables - if you miss an insert statement or two, you could end up with invalid, meaningless, or orphaned data. If you used stored procedures, you could ensure that all operations of that type adhere to the requirement to insert/update all relevant rows.
The bone I have to pick with the notion that business logic does not belong in the database is twofold:
- A database is tightly coupled with the application utilising it as it's data store. To consider the database and the application as separable is not possible - one may not meaningfully exist without the other.
- Business logic is comprised of two main ideas: business rules, and data logic. Business rules belong in the application, data logic belongs in the data store.
My first point should be reasonably obvious and require no further explanation. My second point requires some expansion though...
With the advent of NOSQL and DDD, it is clear that modern data and data usage is increasing in complexity, and in many cases data is being thought about in terms of rich entities that have their own internal structure and governing rules. The rules around what makes a rich object valid are it's business rules. How the data is stored is the data logic. Data logic, or how the object hangs together in storage, is entirely different to whether that form is sensible in the context of business rules. Your data architect may think that a certain object should be stored in the database as a set of rows across multiple tables, or one row as a JSON string - either way the object remains valid in the context of business rules. Data storage concerns are typically different to business rules concerns, and revolve around things like:
- How fast will a CRUD operation be, and will it run risks of locking or delays?
- How efficient are
SELECT operations on the data?
- How many indexes, statistics tables, and other optimisations will be required, and what impact will they have on write operations?
- Is the data uniquely identifiable?
- Is the data stored efficiently given the data types available?
Answers to these questions may change at a different rate or at different times to enhancements in the application. By separating out data logic from business rules, you empower the data administrator to choose the data schema and functionality that is best fit for purpose. By exposing data interactions (both get and set) only via stored procedures, you can retain a consistent interface for the parent application whilst allowing the data administrator the freedom to make changes to the underlying data structure as they see fit.
What needs to be considered though is how much capacity does the business (read: IT Dept) have to support ideological design decisions. It may be the case that you are a small company, where one person does the front end, back end, and database work. You may be in a large organisation where there are teams assigned to each application layer. The less people working on the code, the more broad knowledge they have (q.v. Information Silo), which can lead to a lack of assertions between layers, as the developer knows everything about the process and neglects to include logical assertions. But, (whether large or small) the more indirection and assertions you implement in your code, the longer it takes to write and maintain. You have to weigh up the theory with the practice, idealism and pragmatism. On top of this, you need to consider whether the application has a limited lifetime, what skill sets staff have, how they are managed, how tightly the SDLC is governed, and above all people's personalities and coding habits. This is the horrible and scary world of Technical Leads and Senior Management.
While think about it, here is an example that demonstrates where business rules can validly fit into the database, and rightly sit there. This isn't to be cantankerous, but to demonstrate that aphorisms such as "Business logic does not belong in the database" have exceptions, no matter how noble their idea is:
Let us suppose we have a client management system where people's
details are stored, and changes to those details are stored in an
audit log table. We want a new function in the application where a
moderator can flag a client as having not provided tax details for
this FY, which has the flow-on effect of taxing all transactions at the top rate.
The developer codes up this new functionality but neglects to include an insert into the audit table. They then pass it to a senior
for peer review, and for one reason or another it passes peer review
and enters production. A few months later a client complains that they
are being erroneously charged the maximum taxation rate. The root cause
of the problem is the developer - but there was a business user who
performed the update incorrectly, which is an indication of a gap in
knowledge or training. This cannot be addressed as there is no record
in the audit log table stating which user performed the update and when.
The underlying code issue can be solved, but only time will tell when the
operational issue can/will be addressed.
This problem was a result of ad hoc SQL that performed it's own CRUD operation on a database without considering the company policy that "All changes to data must be recorded for auditing purposes". The database did not complain, because an
UPDATE statement that sets the
invalildTaxFileDetails column to
1 is entrirely valid in the context of the
BIT data type. Yet, the data has now lost it's integrity, which could result in financial and/or reputational risk to the company. In some cases, this may go entirely unnoticed, and clients may be overcharged for their entire engagement period, and then take their business elsewhere due to competitive costing, without realising that they are even entitled to a refund.