SQL Stored Procedures - What They Are, Best Practices, Security, and More...

Want to see a full C# application built from scratch?

Microsoft SQL can be confusing. There are a TON of features, options, and settings to consider when attempting to use it. One of my favorite features is stored procedures. These pre-compiled queries allow you to safely and securely access and modify data. In this video, you will learn the basics of how to build a stored procedure, as well as what the benefits and drawbacks of stored procedures are. You will also learn some best practices, including how to secure your entire database in just a few simple commands.

Thanks to Ralfs HBK for timestamping the video:
0:00 - Intro
1:12 - Demo Data Base overview
2:00 - Creating new stored procedure
7:35 - Calling the stored procedure
10:11 - Changing existing stored procedure
11:12 - Stored procedure with variables
16:51 - Stored procedures: Recap
19:54 - Benefits of stored procedures: Security
22:57 - DB security: Users and Roles
32:49 - Other benefits of stored procedures
34:18 - Drawbacks: No source control
37:36 - Drawbacks: Business logic in DB
41:48 - Drawbacks: Using Entity Framework
43:26 - Summary and concluding remarks

[SmokeySpace]

Out of all the years of watching computer and programming tutorials, you teach things so much better and explain everything and explain it in a way that makes sense, thanks!

[cbuosi]

Im a professional, experienced developer and theres always something to learn from your videos, even with 'easy' topics such as this one. Thank you for your videos. Great work.

[andyhudsonsynthpop]

I've been using Stored Procedures for a least 20 years now, so watched more out of curiosity than anything else. However you never know what you might learn and believe me I have learnt plenty from your wonderful videos. The best of this for me was your commentary on entity framework which just reinforces my belief that I'm better off doing what I've always done. Each to their own I guess, but with so many people now only using entity framework I feel like a dinosaur because I'm not.

[shepniel]

Seriously this is the best channel about programming, at least for me, hope you continue with these videos that are really helpful. Thank you very much!

[bluemoon]

This is by far the best video i have ever seen on this topic, Thank you Tim

[RalfsBalodis]

0:00 - Intro
1:12 - Demo Data Base overview
2:00 - Creating new stored procedure
7:35 - Calling the stored procedure
10:11 - Changing existing stored procedure
11:12 - Stored procedure with variables
16:51 - Stored procedures: Recap
19:54 - Benefits of stored procedures: Security
22:57 - DB security: Users and Roles
32:49 - Other benefits of stored procedures
34:18 - Drawbacks: No source control
37:36 - Drawbacks: Business logic in DB
41:48 - Drawbacks: Using Entity Framework
43:26 - Summary and concluding remarks

[gordonhope]

Tim, thanks for another great video!! Although I am now retired and never employed formally as an "engineer", much of my career was around managing a business that was highly secure and mission critical service network. Stored Procs are the way of life in such environments and, like you, NOBODY felt comfortable enough to executed the Entity Framework in such an environment. The business had to be able to fix ANYTHING that happened in the shortest time possible and performance was an issue. With about 1.5M connected end points sending in asynchronous signals, you can imagine how much data was coming into the network and why performance and security were so important. Kudos to you - you are correct about security!!

[ghulamali]

The way you explain things is amazing.

[shader]

Thanks for the lesson. I enjoy that you teach more of the WHY and not just the HOW.

[leozhang]

Hi Tim, I've watched a dozen of your videos and they are very useful. I'm also learning from you how to explain complex technical ideas in plain English to non-technical audience and even to junior programmers. Thank you! From Guangdong China.

[thenujaliyanage]

Amazing video! Not only do you explain what stored procedures are, but you also give detailed background information and use cases. Keep up the great work!

[DevineAbyss]

This was one of the most understandable, cohesive, well structured and best introductions I have seen in my whole life. I came with no understanding of anything besides SQL-statements and programming (java etc.) and I feel, I have a decent idea of procedures and role-management now.

[johnnyvcrow]

Thanks for pointing out that security matters! It's such a big deal today yet I still see so many tutorials where they completely disregard security matters.

[charliegrayii]

Great channel, excellent mentor, coach, and trainer without a ludicrous price tag. Thank you, Thank you, and Thank you.

[sebastianweise]

Almost 3 years later, i found this, and... well...subbed and THANK YOU! It helped me a lot!

[rtothec]

I'm a MySQL user but I've learned a lot from this video and hope to apply my understanding to MySQL.

That bit about granting regular users only access to stored procedures but not access to entire tables, and then only granting them an execute permission, is the crux on which I hope to predicate a solution to a problem I'm working on. Sorry I know I should not end a sentence on a preposition.

Whereby, I gotta make some data accessible to people who don't really know how to write SQL.

Hopefully I'm smart enough to do the job.

Thanks for the video sir.

[adan-codes]

You are doing the lords work sir. Thank you, I needed this for a project at work.

[raghum]

Very clearly and professionally explained. Thank you so much Tim.

[grone]

This, and the rest of your videos the videos on this channel, is absolutely golden! The SQL course on your site is worth way more than you're charging. You and Derek Banas are The best source for this stuff. Thank you very much, Corey!!

[psykodiak]

First day at work. So glad I found you!