Why stored procedures are a bad idea (and what to do instead)

Most people don’t know what stored procedures are, and even fewer people know why they should avoid them like the plague. I’m going to tell you exactly why you should avoid database stored procedures and what to use instead of them.

What are database stored procedures?

Stored procedures store sets of SQL commands and run them when a specified event occurs. For example, you can use one stored procedure to insert data into the database and another to delete it. This often means putting business logic inside the database. Do you think this is a good idea or not?

My opinion is Business Logic should be contained within the application itself for the below reasons.

Stored Procedures Are Less Maintainable

A stored procedure is a pre-defined computer program that resides on the database server and is accessible to all users. A stored procedure is written in an application programming language, such as T-SQL, or PL/SQL. Unlike Transact-SQL statements, which must be sent from the client computer to the database server for processing, with stored procedures, the code runs on the database server and takes parameters passed by the user when invoked.

Stored Procedures Can't Be Restarted: If you want to stop a running stored procedure, you have to kill it.

Stored Procedures Should Not Be The Standard Procedure

While there is nothing inherently wrong with using database-stored procedures, in practice, they can end up being more trouble than they're worth. For example, if you need to make changes or additions to the code for your application, you might find yourself having to make the same change on many different lines of code. This can be time-consuming and difficult at best. And if something goes wrong and you need help troubleshooting it?

Stored Procedures Are Slow

Stored procedures are often used to store and retrieve data from the database. This is because they have the ability to bypass the query optimizer, which works with SQL statements. However, executing a stored procedure can take much longer than running an SQL statement because it has more overhead. When you execute a SQL statement, you get back results right away as opposed to waiting for the stored procedure to finish executing. This leads to slower performance when using your application.

On Occasions, stored procedures can be faster but you are leaving it to chance that the database optimisers will do the right thing at the right time with the right parameterisation.

Stored Procedures Are Difficult To Test

Stored Procedures in SQL can cause your application code to become tightly coupled. When you have an issue with your application, it's difficult and time-consuming to debug the SQL stored procedure because you need access to both the application code and the database. Even worse, if there is an issue with your database, you may not be able to test the application at all and will need the help of a DBA to fix and debug the issue

Stored Procedures Give Too Much Power To The User

In any application, you'll want to limit the privileges of your users as much as possible. Stored procedures give too much power and authority to the user.

To make things worse, they can't be revoked easily either. They're also not encrypted by default so anyone who gains access to your database can see them all at once. These three drawbacks lead me to believe that if you're considering using stored procedures for anything other than simple queries, you should reconsider and look into something else such as object-oriented programming or REST API calls which will better suit your needs.