Why Not to write SQL Stored Procedures – Part 1

Stored procedures (SPs) have been a workhorse for several decades. They have been used for many reason. Some of the which can be justified, but some can lead to tremendous technical debt.

The good of Stored Procedures

Among justifiable reasons to write SPs are the following

• Performance. This is completely valid use of SPs. Fine tuning queries on large data-sets is crucial for performance.
• Data integrity. To protect integrity of the data SPs can be used to impose certain rules on data. For examples, a database table column may not have NULLs or a string must be of certain size. These conditions can be validated by SP and write operation can be rejected if conditions are not met. However, better alternative approach is to use database Constraints.
• Security. This is valid reason too. Many Relational Database Management Systems (RDBMS) support restrictions on different levels. The restrictions can be placed on “raw” table access, but not on execution of SPs, therefore reads and writes to database would be possible only through SPs.
• Create an interface around “raw” tables so that the database schema (structure of tables) can be changes without rippling the changes throughout entire application.

The ugly of Stored Procedures

SQL Syntax

The worst possible example of using SPs is for implementing heavy business logic. In this case Stored Procedure typically turns into a monster with several thousands lines of code and multiple layers of nesting. In addition one “monster” SP can call another “moster” which makes things even more complicated. As “a cherry on top of a cake” we add transactions, even worse nested transactions withing the SPs.

Fair to admit that above mess was created by violating a lot of good practices and principles and can be recreated in any programming language, like C# or Python. However SQL makes it way more unmanageable when used for implementing entire application business logic.

Please, don’t get this wrong. SQL is a great “tool for the job” of manipulating data. It is declarative, meaning it specifies what we are getting but not how. By looking at the query below it’s very easy to grasp that it returns employee id and name for employees hired since 2020.

SELECT employee_id, employee_name
FROM dbo.Employees
WHERE employee_start_date >= '2020-01-01';

The ugly comes when imperative by nature constructs are heavily used in declarative SQL. Good example is usage of loops and cursors. Below is an example of a WHILE loop iterating over CURSOR which prints employees information.

DECLARE @employee_id as nvarchar(256)
DECLARE @employee_name as nvarchar(200)

DECLARE employee_cursor CURSOR FOR
SELECT employee_id, employee_name
FROM dbo.Employees
WHERE employee_start_date >= '2020-01-01';
OPEN employee_cursor;
FETCH NEXT FROM employee_cursor INTO @employee_id, @employee_name;
WHILE @@FETCH_STATUS = 0
   BEGIN
      Print @employee_id + ' - ' +  @employee_name
      FETCH NEXT FROM employee_cursor INTO @employee_id, @employee_name;
   END;
CLOSE employee_cursor;
DEALLOCATE employee_cursor;

Looks over-complicated? Indeed… Unfortunately I’ve seen a lot of Store Procedures code written in the similar way.

Now let’s compare SQL code above to functionally similar code in C#

IEnumerable<Employee> employees = ...;
foreach(var employee in employees)
{
    Console.Writeline($"{employee.Id} - {employee.Name}");
}

How about in Python? (may not be so idiomatic, but proofs the point).

employees = [...]
for employee in employees:
    print(f"${employee.Id} - ${employee.Name}")

The one can come up with many more examples in different programming languages which would be simpler than SQL alternatives.

Testing

Automated testing is a key to high quality software. It saves huge amount of time by automatically verifying actual software behavior against requirements. Tests expressed in a programming language serve as live documentation which is always up to date. If executed in Continuous Integration (CI) pipeline automated tests will always ensure quality before an application hits Production environment.

Most of programming languages and frameworks have automates testing packages and libraries available with simple to use API, great documentation and and community support.. For example

• Pytest and mock for Python
• xUnit, nUnit and moq to .NET
• jest for JavaScript and TypeScript

For SQL I haven’t found anything similar… Many people take an approach of using one of the above packages with Python or C# and use it to execute SQL queries or Store Procedures.

Another downside is ability to mock. Again, for most of programming languages there are mocking libraries and packages available. Mocking is as easy as writing several line of code. Check how it’s easy to setup a mock in Python.

# Create a mock witch returns a string "I'm a mock!" if passed to str(...) function.

mock = Mock()
# Setup __str__ method to return "I'm a mock!" if invoked.
mock.__str__ = Mock(return_value="I'm a mock!")
str(mock)

# Outputs:
# "I'm a mock!"

However with SQL if we take an approach of automating tests we will have to prepare data in the database before executing test, then execute test and finally query database to validate the result. Writing a single tests becomes a whole big task. Now imagine a Stored Procedure which joins against multiple table and better yet invokes other SPs. How can we test that?..

Conclusion

SQL is great for data querying, aggregation and etc., but it fails to be a general purpose programming language for implementing entire application’s business logic. SQL code growth fast as it’s imperative constructs are far from being concise. Large SPs are hard to read, expensive to maintain and difficult to test.

In the next part we will discuss how to design a simple application with no or minimum Stored Procedures and how this improves scalability and integration with other applications.

Alternative opinions are welcome!

In the next part we are going to cover business logic in SPs from architecture point of view.