Stored Procedures are Better than Raw SQL

Using stored procedures for your SQL operations is better than putting SQL in your application code.

That may seem like a bold claim, but I explain why in this video.

In this video, you’ll learn a few reasons why I think stored procedures are useful. You’ll also learn a few disadvantages of using stored procedures, a couple of which can be overcome.

This concept is relevant regardless of which database vendor you work on.

Of course, you are welcome to disagree with this video!

Let me know in the comments below whether you agree or disagree with this claim!

Timestamps:
00:00 Using SQL in application code
01:12 More business logic
02:31 Business logic in one place
03:09 Helpful for other systems
04:08 Simplify application code
04:54 Disadvantage: version control
05:59 Disadvantage: convincing your team
06:22 Disadvantage: vendor lock-in

[PeteSimpson]

The biggest advantage of stores procedures from my perspective is that sometimes I have to create extremely long and complicated historical queries from multiple tables. If I just used queries from directly within the application, retrieving certain data can easily take 8 to 10 seconds, but using a stored procedure can easily cut that time down to less than a second. The above is an actual example that I came across a few years ago, and yes I was using Index as well.

I don't always use stored procedures as in certain scenarios I prefer direct app access. But for large complicated queries (usually historical) I always stored procedures.

[icns]

A really good primer of Stored procedures. Much appreciated 👍

[hasanmougharbel]

Hey there, keep up the great work in your channel.
I have only a general enquiry as a newbie sql learner.
Could i channel shopify live data into an sql application?(This should be an application developed for a company of 50 employees mainly for the sake of generating reports and analytics))
Is this an easy process to undertake as a sql beginner? What should i keep in mind
and what are the tools or softwares that i should work with right from the beginning.
For notice, I made my mind to work with microsoft sql server, also our company has a dedicated server.
Thanks for taking care of my enquiry.

[marcello]

Con 3:
You usually always have, as you pointed out, specific non standard sql in complexe apps anyway. Triggers are also on top of that. So I guess rewriting the stored procedures only add up a very tiny bit on it. We migrated from oracle to snowflake and didn’t had any stored procedures and it took us a full year. I guess the stored procedures if they would have been present would have added just one week by one person. In total 5 people were involved on and off in the year.

[georgeblazhev]

About version control, there are libraries that help automating the process of applying migrations, so you can have a solution that keeps the sql files and you can track the commits and automate the migrations across different environments with a proper CI/CD pipeline. I was very against stored procedures but I'm coming to seeing their value and advantages the more experience I gain