Why do we need Stored Procedures? | Essential SQL

A stored procedure is a group of one or more database statements stored in the database's data dictionary. Stored procedures can be called from T-SQL using an execute command that can be called from a program outside the database server such as a web-server or a client program.

Why do we need stored procedures?

One reason is for maintainability. Stored procedure provide one location to keep tricky code. So instead of having to replicate a series of program steps or database code at several locations within your database you can put them in one place within the stored procedure and then just call that code from one location which encourages modular programming. And as you can imagine centralized code is easier to troubleshoot.

Another reason to use stored procedures is to encapsulate business logic. In this fashion we can keep rules consistent between calling programs such as programs that are calling our database from either Excel or C Sharp or Power BI. They can call a stored procedure and get the same consistent results as opposed to using a query that they may have constructed on their own.

Also stored procedures provide stronger security. They help assuage SQL injection attacks and they can also control what code a user can see. So if you have sensitive queries, you can avoid having users actually see the query code that is run. They can only call the stored procedure but they can't actually see what SQL makes up the stored procedure. So that gives you control over that as well as, you also have control over who can actually execute a stored procedure.

And then lastly, stored procedures also bring with them some stored efficiencies. When the logic being stored on the server we can avoid some network traffic. So as opposed to having to send multiple commands from a client to a database we can send one command to say execute a stored procedure which then will execute our multiple commands. In addition, stored procedures are cached on the database server so once they are run, they are ready to run again.

[kingtau]

Awesome, I'm sold! I will be getting this course after I get and go through your Fearless SQL course. Wish I had known about this course 3 years ago!

[RussianVideoPodcast]

Best lessons ever . Please post more. Maybe temp tables with examples? Thank you.

[mohammadsi]

Hello Kris, I had some questions about Stored Procedures, finally I got the answers from your video. Excellent explanation! So I already subscribed your channel. Thanks again for your nice explanation.

[Nathan-ngkp]

very concise. thank you for this explanation!