 What's going on everybody welcome back to another sequel tutorial today. We are talking about stored procedures now What is a stored procedure? A stored procedure is a group of sequel statements that has been created and then stored in that database a store procedure can accept input parameters and we will be looking at that today But that means that a single store procedure can be used over the network by several different users And we can all be using different input data a store procedure will also reduce network traffic and increase the performance and Lastly if we modify that store procedure everyone who uses that store procedure in the future will also get that update Let's start writing out the store procedures so we can look at these 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. 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 store 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 sequel tutorial. We're going to go over to a programmability Store procedures and it is not showing up there. What we need to do is we need to refresh our store procedures We're just going to go right here and click refresh and then there is our store procedure Now, how do you actually use this store 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 store procedure was a select statement And so when we actually ran the store procedure, it returned our select statement. Now, let's go down here We're going to make it a little bit more complicated We're going to do the exact same thing in create store procedure Make sure I spell 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 then insert a data into that We are going to add that to this store 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 am inserting that table Now if I create this store 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 sort procedure nothing will be returned So let's write select Everything and we'll just do from and this is temp employee and Right here. And so now let's create our store 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 store 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 tutorials 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 Quoted 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 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 a 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 like this video, be sure to like and subscribe below and I'll see you in the next video