SQL Stored Procedures – why do we need them?
I recently put together a lesson on why we need SQL stored procedures. It is part of my Stored Procedures Unpacked course. SQL Stored procedures are a great way to encapsulate logic securely, while allowing client program an easy way to execute it.
The good news is that writing SQL stored procedures isn’t as hard as everyone makes them out to be.
You can learn more about this this video, it’s transcript follows:
Why do we Need SQL Stored Procedures??
A stored procedure is a group of one or more database statements stored in the database’s data dictionary. SQL 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.
Perhaps maybe a program located on a computer. And in this class we will be focusing on calling search procedures using T-SQL. Also you will find that people call SQL stored procedures stored procs or procs or sometimes S procs. So again, think of a stored procedure as a collection of statements that have been stored on the database and then when you call that one object or collection of statements all the statements in that body will execute.
So here we have an example of a SQL stored procedure. I don’t want you to get too intimidated by everything. We will go through constructing stored procedures and learning about all these elements in a future part of the course. But I think it’s important for you to at least start to learn an overview of what the pieces are. So the first thing I want to point out is that each stored procedure has a name. So here we have a stored procedure called USP Find and Play.
So as you can imagine I’ve named this SQL stored procedure off of what its going to do which is to find and play in our database. This procedure accepts a parameter that allows us to receive an input and then use that input later one in the stored procedure to do something. So we are going to be taking the business entity ID which is a primary key from the human resources employee table, and we’ll be using that to look up employees.
So here we have now the SQL stored procedure name and the parameter. The items in green here are the items that get executed by the stored procedure, so we have a couple of statements that are being run. Set no count on tells the database not to report how many rows are brought back in the query. It’s for efficiency purposes and then we have the meat of our stored procedure which is the select statement.
Hopefully this is very familiar to you. It’s a select statement that’s going into our human resources employee table and selecting a business entity ID where the business entity ID is equal to this value here which happens to be the parameter that was passed in. Again we will talk about the mechanics of parameters later on but just for now assume that if a value of 50 was brought into this parameter that we would be running a select statement where business entity ID equals 50.
When this select statement runs then it results set is returned, so whatever calls this SQL stored procedure will get back a results set of rows of what was found from the select. So in this case it will be a single row of this employee with their, at the TID, their employee number, login, job title, hire date. In summary the stored procedure has a name. There’s inputs to the stored procedure. There’s an execution of the stored procedure and then ultimately there are results that are output from the stored procedure.
Reasons we need SQL Stored Procedures
One reason is for maintainability. SQL Stored procedures 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.
And it’s much easier than the chase-down gremlins or troublesome areas within your database. Another reason to use SQL 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 SQL 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, SQL 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.
Now you should have an appreciation for what a stored procedure is and why we need them. I look forward to seeing you in the next lesson.