When to Code a Stored Procedure — and When Not To

Though it might seem to be a weird title in 2021, you'll be shocked to learn the number of applications (legacy and new) that still use stored procedures.

The stored procedure is a database capability that allows developers to write code at the level of the database and directly manipulate data (and sometimes define data, i.e. create tables, indexes, drop them, etc.,).

Stored procedures have many advantages, including:

1. High performance in data manipulation in comparison to handling this through another application tier, since transferring data across other tiers introduces at least network delay.
2. Abstraction of database structure and logic, where comes all the benefits of abstraction.
3. Ease of DDL execution. Some other languages and frameworks have limitations with DDLs.
4. Direct access to some database features, which are hidden from clients just executing DMLs.

When Stored Procedures Are Discouraged

However, coding to stored procedures is generally discouraged in multi-tier applications for the following reasons. 

Distributed Logic

Usually, multi-tier applications have a business layer that carries the business logic, including validations, orchestration, business rules, etc., making another layer of logic causes the logic to be distributed between multiple layers, violating coherence and separation of concerns

Transaction Management

This is related to the previous point; however, it's more specific to transactions. Transactions should be managed by a single layer, distributing transactions between the business layer. The stored procedure introduces a hard-to-manage burden and usually results in spaghetti code and tough workarounds to manage commits and rollbacks.

Source Control and CI Unfriendliness

Till this moment, it is still very difficult to maintain stored procedure codes on source control the same way it is done for other codebases because it usually exists on the database itself rather than being maintained as a separate codebase that can be manipulated, complied with, or tracked independently on an IDE, and it is also difficult to include this code in CI pipeline (like Jenkins).

When To Use Stored Procedures

In some cases, stored procedures can be useful and considered as a strong candidate to make the job.

Integration Projects

In large enterprises, EAI (enterprise application integration) is a common practice to bring different systems together. This is usually done through a middleware application that supports different interface capabilities, throttling, drivers to backends, centralized governance, etc. This middleware avails services that enable consumers to pull or push data from/to providers. In this case, if the destination (provider) of a service is the database, it is considered best practice to wrap whatever DML statements that query/manage the data into stored procedures for the sake of abstraction.

Utility

Stored procedures can be used as simple utilities even in multi-tier applications to make some helper functionality (ex: data transformation, preparation, etc... )before sending the data back to the application or before inserting the data to tables, however, this option should be considered carefully as using it extensively can lead again to the distributed logic issue

Data-Centric Applications

Some applications include bulk data manipulation between tables in the database and even across databases. ETL tools are not always available or convenient for such tasks. An example is moving millions of records from a table to another after doing some processing on it. These operations are sometimes more suitable to be handled with stored procedures. An extreme case is having the application totally in the database with minor or no interface at all.

Team Capabilities

Now that your company won the project, but most of your available team members have knowledge only of database and stored procedures, you might have no option but to go for this technology in order to deliver. You must consider training your team or bringing in other resources to increase awareness of trending technologies for future projects.

Project Scope/Cost

Sometimes you might get limited with the project scope or cost, consider a situation where you are requested to implement a BPM solution for an enterprise on a certain business domain, however, the scope doesn't include an administration portal for managing the business other than the BPM, in this case, it might be wiser to include the data model and business logic in the database than to put it in BPM so that when an extension to the system is implemented, it will use the existing business code in the database.

Customer Limitation/Preference

If you are implementing the project as a vendor or a contractor, customer might explicitly request such type of code so that he can manage it later, or maybe he cant avail or administrate an application server to include the logic in due to licensing / capacity / team capabilities / other issues.