 Hello, everybody. In this lesson, we're going to be taking a look at stored procedures. Store procedures are a way to save your SQL code that you can reuse over and over again. When you save it, you can call that store procedure and it's going to execute all the code that you wrote within your store procedure. It's really helpful for storing complex queries, simplifying repetitive code and just enhancing performance overall. So let's take a look at how we can create a stored procedure. Now we're going to start by just creating a really simple query. We'll make it a little bit more advanced as we go along and take a look at the different things within the store procedures that you can do. Now let's change this query. Let's say where the salary is greater than, let's do 50,000. Let's actually do greater than or equal to 50,000. We want to include Tom and Jerry as well. So let's go ahead and run this. Now what we want to do is save this really complex code within a store procedure. Let's come right down here and we can create a super, super, super simple store procedure by just saying create procedure and pasting that. Now we just have to name it. So we have create procedure and we'll call this large underscore salaries. And then we do a closed parentheses. Now this is as simple as it can possibly be. It does not get any simpler than this. So let's go ahead and run this. And if we go down, let me pull this up. You can see that it says create procedure, zero is affected. It looks like it worked. And if we come over here to this refresh button, you should see now that under store procedures, it drops down and we have our large salaries. That's exactly what should have happened. We wanted to save that into our parks and recreation. Now if you wanted to be careful, you could say use arcs underscore and underscore recreation. This is not a bad idea, but you don't have to. But you can specify what database within your actual editor window. Sometimes that is helpful. But now we've created it. Now let's see how we can call it. All we have to do is say call. We're going to copy this entire thing, including the parentheses. And let's end it with that's right. That's not my calling. Let's go ahead and run this. And as you can see, it worked because we got the exact output. So we actually called this store procedure. And this code ran. So it's just a select statement. So it worked perfectly. Now you can also come over here to large salaries. And there's this little tiny little button here that looks like a lightning bolt. And if you click it, it's going to open up a different window. And we'll say call parks and recreation dot large salaries. So you can do it that way as well. But we're not going to be doing it that way. Now what we've written right here is not best practice by any means. And I'm going to copy this down here. Because there's a lot of different things that you need to take into account when you're creating a store procedure. For example, this right here is most likely not what you're going to be putting into a store procedure. This is super, super simple. So we'll be having multiple queries. And let's see what happens if I try to put another query in here. And let's get rid of this. So we're going to select everything where the salary is greater than 50,000. Then we'll select everything where it's greater than 10,000, which is everybody. Let's call this large salaries to. So we have two different statements in here. And we want them all to be under this large salaries to let's select everything. And let's run this. And we're getting an output, which is already not a good sign. But we created the store procedure. And then we selected everything. So what's actually happening here? Pull this back down. What's happening is is this is creating the store procedure. And this is just some other, you know, random query. But that's not what we want. What we want is everything or both of these queries within one store procedure. The best practice is to use something called a delimiter. Now this right here is a delimiter, the semicolon. So the semicolon separates our queries from one another. It tells my SQL. Hey, you know, this is a different query. Don't be mixing these and cause errors, you know, that's essentially what a delimiter does. Now we can change the delimiter by coming up here and saying delimiter. And we can change it to almost anything we want. Now in my actual job, I've seen it done many different ways. I've seen these forward slashes. I've also seen dollar signs. This is probably the one that I've seen the most when I worked with data engineers, data scientists, database developers, you know, this one, I see a lot. And then you'll come into the code and you'll say begin. And let's go over here. And let's tab all of this. And then we'll say end. Now when we end, we're going to end it with this dollar sign. So here's what's happening. We're changing the delimiter right here to dollar sign. We're creating our store procedure. And within it, we are keeping all of this. So all of this code is going to go into this one stored procedure. Then at the end, we are saying this is the end right here of this store procedure. These semicolons no longer are the delimiter that's telling us when it is the end of the store procedure. That's what the delimiter does. Now it is best practice at the end to change it back, right? Let me spell it right. Because if you don't, then you're going to have to start using these dollar signs for everything. And how do you spell delimiter? Man, there we go. Now we've changed it back to a semicolon afterwards. So then we can go and write other queries and it'll act appropriately. Let's go down. So this is getting closer to best practice. Let's go ahead and run this entire thing. And if we pull this up, we're not getting an output. That's a good sign. If we pull this up, it's saying we already created number two, change that to three. My apologies. Let's go down here. Now we've created the store procedure three. Now let's go over here. We're going to right click on this. We're going to say alter stored procedure. And now you can see that we have both of these queries within the store procedure. Let's get rid of this and we're going to go and call this. So let's copy this large salaries three, bring this all the way down. And let's say call that store procedure. If we run it, you'll notice we get two outputs. We have six and seven. This result six is where it's greater than 50,000 or 50,000 or greater. This one is where it's greater than 10,000, which is essentially the entire table. Now so far we've done everything just by writing it all out. And that's fantastic. But you can also come over here to store procedures and right click and say create store procedure. Now let's actually copy this. We're just going to create the exact same thing. We'll create store procedure and we can just paste this in here. And let's go ahead and do that. There we go. And sure, we'll call it new procedure. Why not? And if we say apply, you'll notice that it generates this script right here and we can apply it and we can create it and we will in just a second. But let's take a look at it. So we're going to use arcs and recreation. That's what I was mentioning before. We're then going to say drop procedure if exists. Now this is something that I was going to show you later and we'll just show it to you now. Sometimes it is really beneficial to write something like this before you create it in case you've already created a store procedure with that name that you're wanting to replace. So it's checking if it's there. And if that new procedure is already there, it's just going to drop it. Then it comes down and let me see if I can zoom in on this. And then it's going to create our delimiter, which it uses dollar signs. So my SQL is even, you know, validating what I was saying earlier. We're going to use parks and recreation again. And now again, we have to use instead of a semicolon, we're using dollar signs. Then we're creating the procedure, which is new procedure, we're saying begin and then it's even changing the delimiter back. So basically everything I said, this is kind of doing it for you automatically. Now when I click apply, it went ahead and execute that SQL statement. And our new one is ready. So we can go ahead and alter that store procedure. And it looks exactly the same as this one out here, which was large salaries number three. So it looks exactly the same. Now let's go ahead and get rid of this, get rid of this, and let's go down below. The next thing I want to take a look at is something called a parameter. Now, before I actually get into this, I'm going to copy all this down here, because I don't want to rewrite all of it, if I'm being honest. So let's paste this in here. Now parameters are variables that are passed as an input into a store procedure in the allowed the store procedure to accept an input value and place it into your code. Let's take a look at what that actually means. Now, before I do anything, I'm just going to change this to number four. So I don't forget. So let's get rid of all of this. We're going to keep it somewhat simple because we're looking at something new. Now when I say we're passing through a parameter, I'm talking about when we're calling it. So let's say we've already created this one. I'm not going to, you know, run this yet. But let's say we've created it. Let's say I want to pass in an employee ID. I want to pass in a specific person. And I want to retrieve their salary. I know their employee IDs. I just wanted to pull up their salary for us. So what we're going to do is we'll get rid of this. And when we're calling it, let's put this down, when we're calling it, I'm going to pass through a value like one, that's Leslie note. And then I want the salary to be the output. So I'm going to select the salary. So we're selecting salary from the employee salary. But how do we know that this one is the person we're looking for? Well, when we're actually creating this parameter, we create it right in here. That's what tells the store procedure to accept an input value when we're calling it down below. We're going to call this employee underscore ID. Now, after we call it, after we name our parameter, we need to then give it a data type. So I'll call this an integer. So we're telling the store procedure when somebody calls the store procedure, they have to pass through an integer. It can't be a string or it can't be a date. It has to be an integer. Now, what we're going to go do is right down here, we'll say, where the employee underscore ID, that's from this column in the actual table, we'll say is equal to the employee underscore ID, which is our parameter right here. Now, you may be thinking, that's really confusing, their name, the exact same thing. Can I change it? The answer is yes, I actually encourage it. So there are some naming conventions that are out there that I think are helpful, ones that I personally use. But remember, this is just kind of a variable parameter name. You can kind of call it whatever you want. So if I wanted to say HuggyMuffin, I could. And this could be HuggyMuffin. So let's try it with HuggyMuffin. I just came up with that off the top of my head. So don't judge me. But we're going to create the store procedure. And then when we call it later, we want it to return the salary where the employee ID right here is equal to whatever was passed through that parameter, that input parameter. And we're going to keep it as one. So it should return 75,000. Let's go ahead. We're going to create this. And now let's go right down here. And we're going to run it. And we can see that that is the salary and worked perfectly. Now, like I was saying, that is not what I would actually name it. There are some naming conventions like underscore param at the end. So you kind of want to keep it at least I recommend you try to keep it similar to what you're actually looking for. And you can either end it in underscore param, or there's another way you can do it, which is come right over here and do P underscore. And these are just ways that you can tell the code or you can just be able to visually see the difference in the code. So this is just what I recommend. Then you put it right down here. You say where the employee ID is equal to P underscore employee ID saying this is the parameter that's being passed through and put into our actual query. So that is all we're going to take a look at in this lesson. In the next lesson, we're going to be taking a look at triggers and events.