Video Transcription

Welcome back everyone. In this video, we’re going to be taking a look at stored procedures. So so far we have talked about some routines like user defined functions. Routines are nothing more than database objects that are stored as part of your database. But we have some differences here that that start to come out when we start talking about stored procedures. So we’ve seen views and user defined functions with both of them do provide some intermittent level of abstraction, it does insulate your database a little bit from your users, so your users don’t interact directly with the table. So it does provide some security as well. But stored procedures have a little bit more flexibility in that regard, we can also keep all of our error handling in one place. And so we can start to do things like exception caching, and handling like that, which we’ll show in another video. But the primary difference between stored procedures and things like user defined functions and views is that it will start to provide some performance benefits as well. So things like caching, and other things start to happen here, and apart from what you see, in user defined functions and views. But nonetheless, let’s take a look at some examples of this working.

So we’ve showcased and reviewed views and user defined functions, stored procedures are created in a very similar way. It’s just create procedure. As I mentioned earlier, you may see these in the NC standard being referred to as routines instead of stored procedures. But I also have executed this setup query as well, that gets all of our tables back into our database from our our demo. So this has like the person addresses person and address and also our person history as well. But our stored procedure, as I mentioned, looks exactly like a user defined function. Really, the only difference here is, instead of create function, we have CREATE PROCEDURE, it does exist as part of the schema, I can have multiple parameters as part of this, this procedure as well. And just like a user defined function, we’re going to execute some query as part of it. And so let’s go ahead and execute this. So there is our our store procedure now created in our database. So if we go over here to our databases, expand cc 520. There we are. So here is our fetch person, procedure that I just created here. So this is stored physically, in our database, at least the stored procedure part, the data is not stored physically as part of the stored procedure, but the query is the query is still physically stored.

So let’s, how do we actually call this function? Well, this is simple enough, all we need to do is say demo dot fetch person, and then the parameter. These are comma separated out, by the way, if we have more than one parameter, so we say exec this procedure, so execute this stored procedure, and then pass this value as a parameter. So if I give this, if I give this exact command to run, we get this as a result. So that’s person one. And so I run that query up here, that person ID, so select the person information from demo dot person, where Person ID matches the one that was passed in. And notice here I do not have a return statement, there is no return statement here. So the again, very similar as a user defined function, are not going to be using CREATE TABLE commands and things like that here, primarily going to be using the data manipulation language, or DML statements. So select insert, update, delete, we can also use named parameters. So if you remember, syntax from Python, we can use named parameters just like we do there. So we name the parameters.

So at Person ID equals one, so this matches the variable name up there in my stored procedure, so at person, I d equals one, and so you also see this here, right? Demo dot fetch person, person ID. And so then we can give this a run Now that works just the same. As I mentioned earlier, as well, we also have the capability of creating a stored procedure with more than one parameter. So let’s give this a run, get that created. And then we can execute it. And again, I could actually delete, I can remove this, I don’t have to create the stored procedure every every time. But executing that’s gives me all of this. And these have default values, right, we can have default parameter values, as we have in things like Python. So that is very useful. So these are considered as optional parameters, because they have default values, they are considered optional. But I can provide, I can provide parameters with them. So if I wanted to say retrieve persons, that has starts with A J, I can give this around and get only the people whose first name starts with A J. Right? Likewise, I could also Alright, so that’s the first name pattern. The last name pattern, again, this is the the parameter passing here is identical to how Python operates.

So if I give this a run, R is passed in as the last name filter. Now, if you want to skip one, if you want to skip a parameter, you could say default. And now I get all of the people with the last name starting with R, and then a, then the first name uses the default value, which is defined as just the wild card up here. So that works out very well as well. You can also skip a parameter, skip an optional parameter using the named parameters. So if I wanted to, instead of passing in default there, I can say at last name. Pattern, give this a run. Oops, need the equal sign there? There we go. Give that a run. And there we go. So we get similar in similar results, and all three ways. But short answer short story here is that the parameter passing two stored procedures, and the definition of the parameters for stored procedures work very similar in nature as what they do in Python. output parameters are also also supported. And so output parameters you might not have covered yet in your programming classes. But out parameters are very useful ways to return more than one piece of information. So typically, in your programming languages, a function only returns one value has one return statement.

But we can return more than one piece of data utilizing output parameters. And so over here, we can have, we have these three parameters here, first name, middle initial, last name, those are normal parameters. And then down here, I’ve denoted these two parameters as output parameters. And so what the benefit there is, is that for these auto fields, right person ID is an identity column created on has a default timestamp created when a record is inserted. And so I can actually pull those and those pieces back out from our stored procedure, right, we can, so if we give this a run, sorry. This run there we go. So now I have my stored procedure, this whole thing here stored procedure, and notice that my Person ID and my created on on manually setting so created on is the created on field for that person that I just created. And the person I just created. We got that ID from scope identity, if you remember that command. So in order to execute this, let’s go ahead and execute this stored procedure. Exec then demo. Sorry, demo dots and Then we have create person. And then we are, I’m just gonna go ahead and put myself in here. So we have first name. And that is equal to Josh. And then let’s do last name, middle initial is an optional parameter because it has a default value of null, last name. And this is going to do, there we go. Execute this, right. So this doesn’t quite work yet, right? This doesn’t quite work yet, because we still have those out parameters.

So if we actually execute this without defining the parameters, for this, we still get the error. So they work just like what we have, without parameters and things like C sharp. So we need to first declare, we need to first declare our output variables. And then we have to actually add those as parameters here to our exec statement, actually move this into a second line here, so it’s easier to read for us. And now we have person IV, and this is going to be, I can actually use an alias here as the parameters as well, new person ID. And then we have created on as new created on. And now, if I execute this, again, just like Python, right, if we start using named parameters, we have to use named parameters throughout the entire as in the entire result here. So we need to set these equal to likes, so there we go. So let’s give this a run. There we go. Alright, so now that we execute this stored procedure, here, you can see the results of the result of this being created. I’ll try to run this again here. And you can see that we get an error, because I’m trying to insert the same, the same record twice. And so we can actually do another person here.

My son loves Daniel Tiger at the moment. So Daniel Tiger tour database, but you can see now I get that new person ID and created en. And if I run this, again, you can see that you can strain is, is is preventing me from duplicating that data again. But you can see how much simpler this store procedure execution is, in comparison to running a full INSERT statement as a result. So the stored procedure does add some really useful abstraction just like what functions provide us and our normal programming languages. And just so you can see as well, all of the records that we have. We can execute this here, and then if we scroll down your I Am, and here is Daniel Tiger. So still pretty useful, overall, in terms of abstracting things away from away from our users that are interacting with our database. Stored procedures and general are going to provide us that extra layer of abstraction that the typically a database designer or database engineer is going to actually add as part of the database. And then usually, a lot of the times are programmers. So if you’re writing like backend code to a website, for example, most of the time, the the core website, programmers are not going to be writing the actual SQL queries. Those are usually done by a database administrator, usually in the form of a store procedure. So that stored procedure is added to the database. And then the programmer can just pass the data along to the stored procedure to get the results of that query and they don’t have to design those queries by hand because Some, not all programmers are going to have in depth knowledge of database design and writing efficient queries and things like that. So that provides an extra layer there when writing real world applications, but that will conclude our video on stored procedures for now. In the following video we’ll talk about another type of stored procedure called triggers.