 What's going on everybody? Welcome back to another SQL tutorial. Today we are talking about stored procedures. Now, what is a stored procedure? A stored procedure is a group of SQL statements that has been created and then stored in that database. A stored procedure can accept input parameters and we will be looking at that today. But that means that a single stored procedure can be used over the network by several different users and we can all be using different input data. A stored procedure will also reduce network traffic and increase the performance. And lastly, if we modify that stored procedure, everyone who uses that stored procedure in the future will also get that update. Let's start writing out the stored procedures so we can look at the syntax. We'll start off very simple and then in the next one we'll get a little bit more complicated. So the very first thing that you need to write is create and then procedure. And after that you're going to name it. So let's just call this one test and all you're going to say is as and then you're going to write your query. And so let's just do select everything from employee demographics. And that is it. We have created our very first stored procedure. Of course, this is super, super simple, but let's execute this really quick and take a look at it. So it says that the commands completed successfully. Let's go over to our SQL tutorial. We're going to go over to programmability, stored procedures and it is not showing up there. What we need to do is we need to refresh our stored procedures. We're just going to go right here and click refresh. And then there is our stored procedure. Now, how do you actually use this stored procedure that we just created? So let's go right down here and let's say x, which means execute. And then all we're going to say is test. And we're going to run this and there we go. So all we put in this stored procedure was a select statement. And so when we actually ran the stored procedure, it returned our select statement. Now let's go down here and we're going to make it a little bit more complicated. We're going to do the exact same thing in create stored procedure. Make sure I spelled that right. And let's call this temp underscore employee. So if you remember from a previous video, we worked on temp tables and we created our temp tables and then inserted data into that. We are going to add that to the stored procedure so we can see the difference between a simple query versus a little bit more complicated query. So I'm going to say as and then I'm going to insert that in here. Now what this is doing is I'm creating a table and then right down here I'm inserting that table. Now if I create this stored procedure and then execute it, nothing is actually going to be returned. It will insert the data into that temp table. But since I don't have a select statement in this stored procedure, nothing will be returned. So let's right select everything and we'll just do from and this is temp employee and right here. And so now let's create our stored procedure. So that created successfully. Let's refresh over here and let's execute this. So let's just go down right here and say execute and it's going to be temp employee. And now we will execute this and there is our output. Now really quick let's go into temp employee and we actually want to change this stored procedure. So we're going to go over to modify. So when we modify it, a few things are going to show up on your screen. The first thing that you're going to see is it says use SQL tutorial. So it's just specifying the database. The next two things you may not be as familiar with, it's set ANSI nulls and then set coded identifier. If you don't know what these are, it's not super important. The first one just talks about how to deal with nulls when you're using the where statement. And then the quoted identifier just talks about how it uses quotes in the actual query itself. Again, not super important, but they have those automatically turned on. Let's go down a little bit further and we're going to look at the alter procedure. So we created our store procedure, but now we want to alter it. So this is the alter procedure and we are going to add a parameter to this. So what the parameter is going to allow us to do is when we're actually executing the store procedure, we can specify an input into that store procedure so that we get a specific result back. And I'm going to show you what I mean by that in just a second, but let's actually add our input. And we're going to say at, I'm going to say job title. And we need to specify the data type that that is going to be. So let's just say n var char 100. I know below it says var char 100, but that's not extremely important. So this is going to be our input. So we need to go down here and say where job title is equal to at job title. So when we actually are executing this and we say the job title is equal to, let's say accountant, this is going to become accountant and it's going to give us our results based off of it being an accountant. So let's go over here and we are going to click this execute temp employee, which we just modified. And when we run it, we're going to get an error because it is now expecting us to include our parameter of job title. So what we need to do is we need to say at job title. And let's say it's equal to a salesman. Now let's try running this one and see what we get. And so there is our output. If we go back here, I just wanted to show you really quick. We do not have to put this job title right here. You can put this anywhere in the query and use it however you want. That's how parameters work and that's why parameters are so useful. And you can use multiple parameters for one sort of procedure. So you don't have to just limit yourself to one or none. You can put as many as you really like. So I hope that this video is helpful and that you understand store procedures just a little bit better. Thank you guys so much for watching. I really appreciate it. If you liked this video, be sure to like and subscribe below and I'll see you in the next video.