Stored procedures with output parameters Part 19

hello welcome to presume technologies I am venket this is part 19 of sequel server in this session we will learn creating and executing stored procedures with output parameters and learn about some of the useful system stored procedures now before continuing with this session I strongly recommend to watch part 18 of this video series in part 18 of this video series we have seen how to create stored procedures with input parameters in this session we will see how to create a stored procedure with output parameter to create a stored procedure with output parameter we use the keywords out our output if you look at the stored procedure that we have here SP get employee count by gender okay this procedure has got two parameters at gender and at employee count and if you look at the differences between these two parameters at employee count has got output keyword whereas at gender doesn't have it so this output keyword indicates that this at employee count is an output parameter and at gender you know is an input parameter by default because it doesn't have the output keyword okay and if you look at the procedure definition itself you know all we are doing is we are you know we are passing in the gender for example if you pass in male you know as the gender what this query is going to do is it's going to count you know from employee table how many males are there and then whatever count you you're getting you're using that count to initialize this output parameter okay now let's see how to actually you know create the stored procedure so if you look at this we have this employee table and we have caught some males and females now we are about to create a stored procedure which gives us the employee count by gender and obviously to create a stored procedure we use create procedure command so create procedure and then give them stored procedure a meaningful name SP kept employee count you know by gender okay we want to get the employee count by gender and this procedure obviously if you want the total count of employees by gender at the minimum you have to pass the gender information like do you want the male employee count or female and employee count so add gender and this is going to be of type and we're care let's say 20 is the size and then we are going to have another parameter called employee count okay and this is going to be of type integer and I want this to be of type output parameter as begin and so what do we want to do now we want to select the total number of employees you know for the past in gender and to do that we will say select now if you look at this select count of star for example from TBL employee this will give us the total number of employees in the table but we don't want total number of employees we want the total number of you know male or female employees depending on what gets passed into the stored procedure so where gender is equal to now if you if I say gender is equal to male then it will give us the total number of male employees which is seven but we don't want to be hard coding the gender like this instead we want to be using the parameter which the user is going to pass when he invokes the stored procedure okay now when we execute this look at this we get that count and we are selecting it directly and and you know we get the result set but we don't want to do that instead we want to initialize this output parameter so that's why we will say select at employee count is equal to so what's going to happen whatever we get I mean whatever this count function returns instead of selecting it as a result set we are passing it to the output variable so the output variable gets I mean output parameter gets initialized okay and instead of saying star it's always good to use the column name there so let's use ID so that's it so let's create the stored procedure so we have successfully created a stored procedure with an output parameter all right now to execute the stored procedure with output parameter now we have created the stored procedure we want to execute that okay now usually we have seen how to execute a stored procedure with input parameters in the previous session okay to execute the stored procedure with output parameter it's slightly different okay now you know that this stored procedure is X is accepting two parameters one is the gender parameter and the other one is the employee count gender is an input parameter employee count is an output parameter okay now obviously input parameter means you have to pass something to the stored procedure where as output parameter will return something back to you okay now when a stool procedure return something back to you you want to hold it somewhere obviously in a variable that's why you first create a variable to receive the value okay so I'm creating a variable called @ employee total and notice the data type of this variable should match the data type of your output parameter why because essentially we get a value of this data type back so we want a variable you know basically to hold the value that is going to come out of the stored procedure so that's why we are creating a variable of integer datatype okay and then when we actually execute the stored procedure look at this we are executing the stored procedure here we pass the value for gender which is nothing but the male and then what is this query going to do it's going to count the number of males and then it's going to initialize you know this output parameter okay so we are passing in a variable which is going to receive that count in this case at employee total receives the value and then finally we that value so let's see how to execute this okay so obviously to execute the stored procedure okay first we need to declare a variable let's say at total count maybe of type integer so the data type of this variable should match the data type of the parameter the output parameter okay and then what you're doing is you are executing the stored procedure which stored procedure is that SP employee get count by gender and then when you execute look at this when you execute that stored procedure obviously you have to pass in four you know which gender do you want to count the number of employees male or female you have to pass that input value for that input parameter but whereas for the output parameter you don't have to pass a value you will get a value back so here we want to check the count of male employees and then we pass this variable to receive the value back okay now when you pass that you need to specify the output keyword otherwise it will be now okay so that's why to indicate to the stored procedure okay this at total count is an output parameter you have to specify here that you can either say out or output it doesn't really matter okay and then finally what you can do is print the value or you can pass it to another stored procedure or do anything you want okay so when we execute this we should get that value back which is seven here okay now look at this if I don't pass the output keyword there and if I try to execute that look at this it doesn't print anything because this is null okay it doesn't receive the value if you don't specify the output keyword and you know when you declare it if you if you haven't initialized the set total count to anything it will basically be now and you will end up printing now and to quickly check that you can check that here if you know maybe at total count is now maybe we want to say print at total count is now all we are doing is we are checking if it is null or not else if it is not now we want to print a message saying that it is not now print at total count is not now now if we execute that look at this we didn't specify the out keyword if we don't specify the out keyword this variable will not receive the value and since we haven't initialized this variable anywhere else it is going to stay now and if it is now it will print this line so let's execute this so when we execute these lines look at this at total count is now but on the other hand if you pass in something you know if you pass the out keyword what's going to happen this variable is going to be initialized with the count of employees of that gender okay so now when I execute this you should get the message saying that at total count is not now which means it has got some value all right so keep that in mind whenever you execute a stored procedure with output parameter you know for that output parameter you have to specify the out keyword otherwise it will not be initialized and it will always stay now all right okay so if you don't specify the output keyword when executing the stored procedure the the employee total variable will be null and we have just seen that okay now here you know we are passing look at this we are if you look at the stored procedure the first parameter is gender parameter and the second parameter is the employee count so we are passing in in that order okay so the first value will be used by this parameter the second one will be used by this parameter okay now if you use the parameter names when you're passing values to the stored procedure then the order doesn't really matter for example look at this let's say I want to pass my output parameter first how do I do that you have to use the parameter names okay let's get rid of this here okay so what's the parameter name it's at employee count so that at employee count is equal to this output parameter and you still should have the out keyword and then you can say at the gender is equal to I want to pass male okay now look at this I altered the order in which we are passing the parameter bus parameters but since I specify the names of the parameter the stored procedure knows which value is for which parameter okay so obviously if I execute this now it should work without any issue so we get seven okay seven male employees all right there are some extremely useful system stored procedures which we can use for a variety of purposes for example here I have this SP underscore help procedure it's a system stored procedure and in the last session we have learnt that you know any procedure that starts with SP underscore is usually a system stored procedure and user-defined stored procedure shouldn't be having that name okay so if you see this SP underscore help system stored procedure this procedure can be used to view the information about the stored procedure like parameter names the data types etc okay let's see this in action now if I want to find out more information about you know the stored procedure that we have just created I can use SP underscore he'll take the stored procedure pasted there and when we plus you know select those two and when I press execute you should see okay name of the stored procedure and look at this the type is stored procedure this is a stored procedure and when we have created it the parameters it has got their data types etc okay so you can use SP and s Co help or if you don't want to use that system stored procedure you can you know go to programmability stored procedures folder refresh and you should see your stored procedure there and if you expand that you should see the parameters that it is expecting all right okay now this SPN does go help you know you can use that with any database object for example tables we use triggers etc for example look at this when I use this SP underscore help with this TBL employee what information do I get I get all the information about you know the different columns that are present in this table there data types etc any indexes this tables this table has got any constraints this table has got basically you get the information about the object okay it could be table view stored procedure trigger etc so SP underscore help work with any database object and if you don't want to use the system stored procedure you can graphically look at the information about any database object again or what you can do is simply highlight that object and press alt f1 that's a keyboard shortcut and you you get the exact same information again okay so that's about SP and ESCO help system stored procedure and in part 18 of this video series we have seen how you know what is the purpose of SP underscore help text if you want to view the definition of a stored procedure we use SP underscore help text system stored procedure okay similarly even if it is stored procedure with output parameters you can still use SP underscore help text system should procedure ok press f5 you should see the text of the system stored procedure and remember in part 18 we have seen how to encrypt a stored procedure if you have encrypted a system I mean a stored procedure you cannot view the text of that stored procedure ok and finally this SP underscore depends which is really useful ok this stored procedure basically is is used to view the dependencies of the stored procedure for example if a stored procedure depends on a table you know look at this this stored procedure depends so this stored procedure where are we getting the information from the stored procedure is getting the information from TBL employee table so this stored procedure depends on this table okay so when I execute this so we get that information okay this stored procedure is depending on these columns ID and gender present in these in this table TBL employee okay now how is this useful this is useful because let's say I want to drop TBL employee table okay if I drop when I say drop I'm going to delete that table so if I delete TBL employee table and if I try to execute the stored procedure what happens it will throw an error because this stored procedure is using that table and if that table is not present obviously we get an error okay so I can quickly check okay are there any any dependencies and if there are dependencies I wouldn't delete this table okay so what happens if I delete it instead of deleting it let's rename it to something so TBL employee 11 I have just renamed it to that it's as good as deleting it and now if we execute the stored procedure look at what's going to happen it will throw an error saying invalid object named TBL employee okay so you can quickly check okay what are the dependencies okay so since you want to delete this table you can check that table itself so you can use that TBL employee table here and when you plus a five look at this there is a stored procedure that is referring this TBL employee table okay so if you look at the messages here in the current database the specified object which is nothing but you know TBL employee the specified object is referenced by the following which is nothing but the system stores here so it's saying as this two systems - I mean this stored procedure is dependent on this tvl employee table so deleting this table may affect the stored procedure okay so we'll be cautious when we are about to do that so SPM score depends view the dependencies of the stored procedure this system stored procedure is very useful especially if you want to check if there are any stored procedure that store procedures that are referencing the table that you are about to drop SP and the score depends can also be used with other database objects like tables etc on this slide you can find resources phrase without net and c-sharp interview questions that's it for today thank you for listening have a great day

Channel: kudvenkat

Views: 616,566

Keywords: Creating, executing, Stored procedures, output parameters, sql output clause, sp_help stored procedure, sp_helptext stored procedure, sp_helptext stored procedure in sql server, execute procedure with in and out parameter, proc sql count output, sql execute output example, sql server exec output example, how to output row count in sql stored procedure, sql exec sp output parameter, sql execute output variable

Id: bldBshxuhMk

Length: 18min 20sec (1100 seconds)

Published: Wed Aug 22 2012