Stored procedures in sql server Part 18

welcome to presume technologies I am venker this is part 18 of sequel server in this session we'll understand what a stored procedure is a simple stored procedure example creating a stored procedure with parameters altering a stored procedure viewing the text of a stored procedure and finally we will see how to drop a stored procedure a stored procedure is a group of transaxial statements if you ever have a situation where you have to write the same query over and over again you can save that specific query as a stored procedure and call it just by its name let's understand what we mean by this with an example now I have this table called TBL employee which has got you know the ID name gender and department ID columns let's say I want name and gender of an employee so we type in select name agenda from TBL employee so every time I want the name and gender of an employee you know I have to write this query okay instead of that what we can actually do is wrap this query inside a stored procedure and call that stored procedure rather than you having to write this query again and again so how do we create a stored procedure to create a stored procedure we use create procedure command so create procedure and then you have to give this procedure an name okay so SP let us say get employees okay since this procedure is getting us you know the employee name and gender I'm giving it get employees and look at this in the name I have this letters SP a common naming convention for store procedures is that we usually prefix that with small letter S and small letter P okay indicating that you know just by looking at this name you can tell okay this is a stored procedure alright so create procedure procedure name and then you will use as begin and and okay so the definition of your stored procedure goes between this begin and end okay so this is the body of your stored procedure okay now when I execute this command what's going to happen is a stored procedure with this name gets created in this database which is nothing but sample that we have selected here okay now if you want to look at the stored procedure that you have just created you know you want to make sure whether if this procedure is actually created or not okay go into that database which is sample and then you have this folder called program ability expand that and you should see a folder called stored procedures if you expand that you know we don't have it currently listed there just refresh that and you should see that stored procedure which we have just created which is sp get employees okay so anytime you want the name and gender of an employee instead of writing this query again what you can actually do is execute the stored procedure okay so if you want to execute you just need the name of the procedure so what happens when I execute the stored procedure okay to execute the stored procedure you just highlight that and click execute and you get the name and gender you don't have to write that query any more now you might be wondering it's a very simple query why don't I write that rather than having to create this procedure and then invoke it now this procedure may be simple in reality the procedures will be long you know there are very no stored procedures with over three thousands of lines for example okay and not only that there are several other benefits of using stored procedures from security to network reducing network traffic etc we will be talking about the advantages of stored procedures in a very great detail in a later session ok so we use create procedure or create proc statement to create sp I mean you can either say create procedure or you can just say create proc for shortcut ok to create a stored procedure we will talk about the naming convention of the stored procedures in just a bit okay and to execute the stored procedure we have seen how to execute that stored procedure you know you just copy that the name of the stored procedure and you click this execute button and what happens the sequence statement within that short procedure gets executed and it returns the name and gender columns all right so that's one way to execute it or you can use the exact keyword and then click this or you can use a full execute keyword and then again plus f5 or you can also graphically execute the stored procedure just right-click on the stored procedure and select execute stored procedure and the moment you do that it shows this vendor this procedure doesn't have any parameters otherwise you will have to supply the values for the parameters in just a bit we will see how to create a stored procedure that takes parameters now when I click OK it executes that stored procedure look at that alright so those are the different ways to execute stored procedure now let us look at a simple example of how to create a stored procedure with parameters okay so let's go back to that table TBL employees all right now what I want to do is I I want to create a stored procedure which takes two parameters maybe gender and the department ID okay for example if I pass gender as male and Department IDs one tier stored procedure it should give me employees only within that gender and within that department okay so your store procedure needs to have these parameters okay so let's see how to do that so as usual to create a stored procedure we use create procedure command so create procedure and give your procedure a meaningful name so SP get employees by gender and department okay so I want the employees by gender and Department now look at this now this procedure okay the user who invokes your stored procedure is going to pass the stored procedure the gender and the department ID so for them to be able to pass the values for gender and department ID they should be parameters just like how functions have parameters in c-sharp or any other programming language stood procedures can also have parameters okay so one is the gender parameter and if you look at gender it's text here so the data type is going to be n where care of maybe 20 and department ID Department ID is going to be integers or Department ID integer as begin and so the definition of your stored procedure goes in between these lines okay so what do we want from the table we want the name and maybe just the gender and also the department ID from which table we wanted from TBL employee table but we don't want all names genders and department IDs we want to filter them with what the user is going to pass in is going to pass in the gender and department ID so we are going to say the gender column here should be equal to whatever the user is passing in at gender and along the same lines and department ID let's bring this to another line is equal to whatever the user is going to pass in okay so these parameters are like placeholders when users execute your stored procedure they're going to pass in values for this gender and department ID which will be replaced at execution time okay so let's create the store procedure so to create that select the entire stored procedure click execute button command completed successfully now if you refresh the stored procedures folder you should see SP get employees Genda and department okay now to execute the stored procedure I just need the name of the stored procedure and look at this this stored procedure now is expecting gender and department ID parameters now look at this if I don't pass the parameters and if I did try to execute that stored procedure see highlight that and then plus execute what's going to happen this procedure or function SP get employees by gender and department expects parameter add gender which was not supplied and that makes sense it's expecting a gender parameter which is not supplied so we need to pass in the gender parameter since gender is of type and we're care I have to use single quotes so I want the male employees within you know department ID 1 so department ID is 1 so these are the two parameters that this stored procedure expects and we need to pass them so when I press f5 now look at that I only get male employees within that you know department ID 1 okay on the other hand if I want all the male employees and department ID do a 2 I can do so all right now when you have parameters like this you know what you're doing is you're just passing in the parameters so so so this male value will be taken into at gender parameter where as this number 1 is passed into department ID parameter okay now what happens if I put it the other way on I am passing one first okay so what's going to happen it will take this one into gender and one is an integer but gender is of type and where cab and this one will be converted into n we're care implicitly no problem but it comes to the second parameter male it tries to take this into Department ID parameter and if you look at the data type of department ID parameter it is integer okay so it tries to convert this string into integer and it fails and it throws an exception look at this if I try to execute this now I get an exception saying that error converting data type where care to integer so it is trying to convert this mail you know string of type and where care into integer and we get that error okay so when you have multiple parameters that the stored procedure is expecting and if you're passing just values the values order you know the order in which you pass them is important okay the first parameter will be used I mean the value here the first argument will be used with the first parameter and the second argument will be used with the second parameter okay that's why the order is important but if you use the parameter names like this let's say I want to pass one two at Department ID I can specify the name of the parameter like so and similarly I can specify the name of the parameter for gender so when I execute this now I will have no issues because you are specifying the name of the parameter okay so sequence of a knows okay this one is meant you know to be the value for Department ID parameter and mail is the value for gender parameter it's only when you don't specify the names of the parameter the order of the the order in which you pass the parameter is parameters is important alright and okay so we have seen how to create a simple stored procedure and how to create a procedure with parameters as well and we have seen how to execute them as well okay now once you have the stored procedures let's say I have created two procedures until now SP get employees and as we get employees by gender and Department now if I want to view the text of these two procedures what are the different ways that are available one way is to simply right click on that stored procedure script stored procedure as create two new query editor window this you know generates the contents of that stored procedure look at this this is the stored procedure definition that we have created create procedure procedure named as begin and and then our query this is one way to look at the definition of a stored procedure and the other way is to use a system stored procedure you know these stored procedures that we have created here are user-defined stored procedures these are not system stored procedures now sequel server you know has some system stored procedures defined okay and we use it for certain tasks for example I want to find the text of a stored procedure how do I do that I can use a system stored procedure called SP underscore health text okay so look at this this is the name of the system store procedure SP underscore help text okay SP help text and then if I pass in the name of the stored procedure there SP get employees and then when I select them together and execute this look at this I get the text of my stored procedure you can then copy that paste it here and see how does the implementation of the stored procedure looks like okay so to view the definition of a stored procedure you can either right-click on that script stored procedure as create two new query editor window or you can use the system stored procedure SP underscore health text followed by the name of the stored procedure which gives you the text of the stored procedure okay now in this slide if you look at this you know whenever we name user-defined stored procedure microsoft recommends not to use SP underscore you know prefix for user-defined stored procedures because the reason is system stored procedures has that prefix okay now if you happen to use SP underscore prefix for your user-defined stored procedures there are two problems number one there will be an ambiguity between user-defined stored procedures and system defined stored procedures just by looking at the name we cannot tell you know is this a user define stored procedure or system defines stored procedure okay and another problem is with future releases of you know new sequence of a version there may be name conflicts you know if you create let's say SP underscore ket date just you know stored procedure and in the future release there is a system stored procedure which is you know similarly named SP underscore get date you know it's going to conflict with the user stored procedure okay so to avoid problems like this it's always better not to prefix user-defined stored procedures with SP underscore prefix alright so to change the stored procedure now once we have created a stored procedure for example I have the stored procedure SP get employees after I have created the stored procedure let's say I want to change its implementation in some way how do I do that let's say at the moment when I execute this SP get employee stored procedure I am NOT getting the names sorted you know I mean the names are basically not sorted I want them to be sorted so how do I do that I will use the order by Clause so order by name okay so I am changing the implementation of this tour procedure now if I execute this create procedure once again look at this we get an error stating that there is already an object named SP get employees why we already have that and we are trying to create that again with the same name so obviously we get that error our intention here is to change the definition of that stored procedure not to create another stored procedure so if you want to change the definition of the stored procedure then you say alter procedure and I press f5 the stored procedure gets changed now if we execute that we should have the name sorted okay so we use all the procedures statement to change the definition of a stored procedure and to delete the stored procedure we use drop procedure procedure name just like you know if you want to drop a table you will use drop table table name okay so similarly to drop a procedure you will say drop drop procedure and procedure name for example I want to drop or delete SP get employees you know I just pass it there I press f5 and then if i refresh the stored procedures folder it's gone now it's deleted or what you can do alternately right-click on that and select delete okay now it's also possible to encrypt the text of the stored procedure and it's very simple to do for example I have the stored procedure now as we get employees by gender and Department look at this now this is not encrypted at the moment so when I use SP underscore health text and when I press f5 I am able to get the text of that stored procedure so that's how the stored procedure is implemented now if I want to encrypt the contents the text of the stored procedure I can do that how do I do that all you have to do is to use this switch this option with encryption okay and we want to alter that so I will say alter now when I press f5 look at this command completed successfully and now the moment i refresh this folder look at this we get a little lock symbol there indicating that this stored procedure is now encrypted okay now if somebody tries to you know get the text of the encrypted stored procedure we get a message saying that the text for the object is encrypted and and we cannot retrieve the text of that ok and you get the same kind of message when you kind of use script stored procedure as create two new query editor window okay we get this error box you know if you look at here it says that text is encrypted so once a stored procedure is encrypted you cannot view the text of that stored procedure but however if you want to delete the stored procedure you can go ahead and delete it and I'll just right click and select delete it gets deleted but you cannot view the contents of his stored procedure that is encrypted all right in the next session we will see how to create an invocation procedure with output parameters in this session we have seen how to create a stored procedure with input parameters in the next session we'll talk about creating stored procedures with output parameters on this slide you can find resources for asp.net in c-sharp interview questions that's it for today thank you for listening have a great day

Channel: kudvenkat

Views: 1,518,930

Keywords: stored, procedures, in, sql, server, 2008, stored procedure input parameter, stored procedure with parameters in sql, sql stored procedure parameter example, sql stored procedure with parameters, alter stored procedure sql server, modify stored procedure in sql server, sql execute stored procedure, sql stored procedure encryption, sql procedure alter, stored procedure basics in sql server, stored procedure youtube, delete sql stored procedure, drop sql stored procedure

Id: Qu3E-oncF3g

Length: 20min 10sec (1210 seconds)

Published: Tue Aug 21 2012