 Yeah, we do another one here kind of interesting. So let's write another procedure down here We'll get that data from let's just say the same table person of person is okay, so what do create procedure SP and we'll get this from What it what you what I use I think it's different table mission I use the human resources Yeah, so the human resources will join that with another table The person in person table okay, and what we'll See that works the way ahead of here. So I'll just say SP employee Employee Like that and we'll have a parameter will select the name type. So we'll pass the string Here we'll go by the just the last name The last name. This is a voucher. We'll leave it at you don't know how many characters so we could put 50 Okay And we don't return that that back. So I'm just even asses and then As Put it in here. So we don't forget okay and so what I select from the Select Everything it's okay. Well, let's see. We can limit that everything. I see what's inside that person that person table Person that person We have the First name last name. Okay, let's say Let's start for now from the person that person table with a P P I'm going to join that with the human resources that employee and the H E on If he's that I think the business entity With the H E that business entity So we join those two together Okay, and I want to get Just the first name. So he that first name Okay First name will also get that last name What else would it get? H E Job title Then hiring date. Okay. So I say we get those four fields from those two tables This will give you everything but we won't limit that only by a certain employee. So we'll put a where clause in here Where the last name? Okay, so the last name So to say that last name People equal to people equal they have to have exact match right We could do that to say equal to the last name up here So match the last name in the variable name. So what I want to be passed to it will pass will Match that exactly last exact last name and we always make we've always checked to make sure I go is Tested first instead of that our last name. You can say just Just just to make sure it works before you run it you can do that declare Last name As you are char And set that to just say the name of will be a name in here See The pick like Last name is like Duffy or Sanchez Or I think that that accent symbol so it's a Miller Equal Miller here And we can Run this just to make sure it works before we actually Create a procedure. So we have three with the name Last name Miller and this case you have an exact match. Okay? letter for letter And so on so if you have Miller's May not be trying anything. I don't know. Let's find out So there's no Miller's Okay, so at least we know that that actually that works so we can take this back up And then we can turn this into a procedure Create that And then execute So we get our script. You just need one parameter. I did put any default value. So it requires one Okay, so down here we can execute that xc sp by name and we'll pass a string of Again, just test. We know Miller is there. So we pass that Miller string to it. We run should get three Okay, so before So in this case, you can write this into a script and you can pass it to you know And user say use this function to search for a particular employee by the last name So they don't need to know what's going on inside this black box. You can encrypt this or you put this to it Yeah, I can call this function within the view I think you can Yeah, I'm sure you can because you can also have functions procedure method But anyway, you can get this procedure out there to the employees and then encrypt this so they don't know what's what table Calling them to say has that you've got the data back Okay So if I put another Miller if I put like a maybe a Johnson just guessing here I see that returns also three so this one here is fine. It's not The best but you have to be if it matched exactly to that last name So if you want to be a little bit more I Make it a little bit more Russell don't you can say instead of equal You could say like right? Like the last name it could be If you want to say I have to start with this or I can say start with anything Has the last name And then end with anything Right So you have the two wildcars there it could be if you put like Sun in there you get Johnson you get if you put John you get Johnson back So this is a little bit more Flexible in terms of just where you were searching you make search of the database. So if I do this and if I Go ahead and so you always want to put a default value just in case you don't put it You can say the character will be here just blink I'm not sure think what works, but so Gonna alter this You already created it if you do create it's gonna say oh this already exists So you have to drop it Nice to alter that and then execute So now if I run just with the John Should grab me everything where the word John in it So only three in this case I put Just the old should get a lot of those Let's see. I get about 98 of them. So you can see how we use this in the past and it's Being used in this scenario and of course many more Now we have to learn about I think I skip the entire part on the sub query But if you are curious, you always go back and do the sub query That means you can call another query inside a query here Okay, so like this variable name I could say instead of just last name here you can call another Function here and it will another sub query and we turn the string back so you can match that But so That's pretty easy to do straight for I can say to get all the joint statements you can Put that in there What else? Are there any questions? Well, so you can also do something with the learn about the structure last week So here's a set of blank for this into a normal no value Right They can do like so when I when I do this and if I also this Okay You run without the parameter Right. There's nothing returned because it could know You can do it a block in here, right? We learned about that last time so you can say If this is not know then run this otherwise run something else You can also this game make it a little more complex You could say here if the last name is Not know Right, you cannot say equal to know. It's not because no in these cases not a string So if that is not know then this whole thing Will run because we have the variable name come in here as in madam. They're very variable You run all of that and then else to run the other Part here without the work loss for example, you can do that and they end it here So if they come put the name run the filter otherwise run it without the filter you have everything right, so if I were to Go ahead and alter this. I mean, yeah safe Also this Oh, I need to put They could put begin here. All right. Yeah, so they don't match that Confusing so let's see if this will work This year. Okay, so we got that in Just that I need to put it again for the else and so now if I run this without the Parameter if she grabbed everything from this table and Everything but yeah in one. I selected four columns. I get two hundred ninety of them That's entire table or when they match that many anyway, if I put a parameter for all And I'll get only those filter which is only 98 that put back with a Chole They get only three. Okay, so this is what those if and else part comes in