When To/Not to Code a Stored Procedure

Though might seem a weird title in 2021, you might get shocked with 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 Are Stored Procedures Discouraged

However, coding to stored procedure is generally discouraged in multi-tier applications for the below 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 and stored procedure introduces a hard to manage burden and usually results in spaghetti code and tough workarounds to manage commits and rollbacks.

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 integrate 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 have only knowledge with database and stored procedures, you might have no option but to go for this technology in order to deliver, however, you must consider training your team or bringing other resources aware of trending technologies for future projects.