Are you trying to convince developers to use Stored Procedures rather than inline SQL or an ORM, say Entity Framework? It may seem futile, but that doesn’t mean you can’t try. Let’s at least fight for the code we know belongs in one. Erik Darling recently created an article on switching from an ORM to Stored Procedures. I recommend giving it a read.
This article is the first part of a series highlighting the benefits of using Stored Procedures. When I reference not using Stored Procedures, I’m lumping inline SQL, LINQ, EF, ORMs, and random SQL scripts saved on Carl’s laptop. Here are a few examples.
var SqlQuery = "SELECT * FROM SalesPerson WHERE Id = 109"; OR var salesperson = mapper.salesperson.FirstOrDefault(x => x.Id == 109)
I’m sure we could list others, but you get the idea. Let’s define the platform as SQL Server. The concept applies to other RDMS.
Why Use SQL Stored Procedures
Frequently, developers ask why to use Stored Procedures in code versus inline SQL. The answer I provide is, it depends. Most people hate hearing this reply. To hardcore SQL developers, that’s heresy.
Your development team might need convincing that packaging SQL in Stored Procedures is the ideal path. The items presented below will provide a strong argument.
One of the key benefits is maintainability. Like most things in life, you won’t stick to it if you can’t maintain it.
Suppose you’re trying to create a habit of going to the gym. This first step might be to make getting to the gym easy. In the same vein, a Stored Procedure can make managing business logic simple. Especially when you compare having the same code embedded throughout your application.
Making SQL Changes Easy
If you need to make changes with inline T-SQL, you’ll likely do so in several places. Stored Procedures typically require updating in one spot. In my experience, updating Stored Procedure’s are more manageable than updating the application code. Deploying changes via T-SQL is often less impactful in a production environment. It should go without saying that deployments will vary based on your unique situation.
Anyone who works with SQL knows you need to run manual scripts. For example, every quarter or month, you might add rows to a table. Imagine someone having the responsibility of running this script. What if that person goes on vacation? Worse, what if they leave the company? How many times have you heard so-and-so ran a script a year ago for me? You don’t want essential scripts under one person’s control.
Also, how are we sure our DBA isn’t running an outdated script? In this instance, wrap the script in a Stored Procedure. This way, we’re saving in the database and hopefully a code repository application like Git. If you’re not using source control on SQL scripts, please consider doing so.
I’ll be the first to admit that my bias towards SQL clouds my viewpoint. Yet, I’ve seen developers use deeper thought with Stored Procedures than LINQ queries. Perhaps it comes from making T-SQL the focus. The following quote comes to mind.
“The main thing is to keep the main thing the main thing.”
– Stephen Covey
Troubleshooting SQL
Troubleshooting performance issues are commonly more manageable with Stored Procedures. With a Stored Procedure, you know what query is running. You can then execute it outside the application. Remember to do so in a test environment.
If you’re a modern version of SQL Server past 2014, I suggest using Query Store. Erin Stellato has written several articles on the topic. One of Query Store’s many features is the grid that shows the Stored Procedures name. Please see the screenshot below.
The data doesn’t persist, but a dynamic management view, sys.dm_exec_procedure_stats, details a Procedure’s performance. You can save the results to a table. Keep in mind the results are an aggregate over all executions.
Another tactic I employ includes logging performance metrics directly into a Stored Procedure. You can have a start and end time that tracks execution time and counts.
Implementing a debug flag makes troubleshooting smoother. Erik Darling crafted an excellent article on applying a debug flag with larger Stored Procedures. I recommend giving it a read.
Use Your Best Judgment
Lastly, I’ve written how maintainable Stored Procedures are over, let’s say LINQ. However, I wouldn’t advise wrapping every database call into one. In the same way, you want the habit of exercise to be easy.
You want it easy for developers to build quality apps. To paraphrase Wendy Wood, author of Good Habits, Bad Habits, you want to reduce friction for behaviors you desire.
Besides, if you have one data engineer, it’s likely impossible to support hundreds of Stored Procedures.
As a general rule, build complex code into Stored Procedures, no exceptions.
Wrapping Up
In summary, convincing dev teams or leadership to use Stored Procedures may seem impossible. The difficulty increases if you’re the lone data engineer on the project.
Unless your coworker’s focus is SQL, they won’t share this belief. Yet, going into a design session with an open mind can be helpful. Besides, offering maintainability as a critical benefit may get complex code into Stored Procedures. At the end of the day, every team member wants to deliver a quality product.
Please let me know if you have any questions. Leave your experiences in the comments below.