SQL Server Programming Part 4 - Output Parameters & Return Values

welcome to this wise ell tutorial in this session we're going to teach you how to use output parameters and return values in Microsoft sequel server what we're covered in this session is a couple of techniques for returning values from store procedures we're going to start with a quick recap of input parameters and then move on and show you how to define output parameters we'll show you how you can get the result of an output parameter in a calling procedure and then finally we'll show you how you can use return values in store procedures as a simple alternative to output parameters so let's get started before we start talking about output parameters I just wanted to give you a quick reminder of how input parameters and basic stored procedures work so what we've done here is created a basic procedure which accepts a single parameter called apt year and the data side of that is an integer the value that's passed in is used in the where Clause of a simple select statement so we're gonna return a list of all of the films made in a particular year we've already created this procedure so just to show you how you would then execute it in a separate script we'll exact or execute SP films in year and we've named the out year parameter and passing the value 2000 on XQ this script I'll see a list of all the films made in the you that I've requested what I'd like to do next is give my procedure the ability to pass information back out when it's executed so here I'm passing information in by a parameter I'm going to create two new parameters which will pass information back out to the calling procedure for this example we're going to pass out a comma-separated list of the film names and also a count of the number of films in their list to add these parameters to the stored procedure we need to head back to the script which we used to create it in the first place modify the create keyword to the alter keyword instead and then I simply need to add a couple of extra parameters to the parameters list so I usually do this on a separate line with a comma at the start of each line I'm gonna have a new parameter called at film list and the data type of this will be varchar' Max and I can spell Bachar that would help excuse me oh yeah I'm also going to add another parameter called @ film count and the data type of this button is going to be int again now at this point these parameters are both input parameters to switch the mins being output parameters is remarkably straightforward you simply add the word output to the end of the definition of the parameter so there we go we've created two new output parameters once I've defined my output parameters the next thing I need to do is tell them what values they will return to the calling procedure I'm going to show you that first with the film count parameter because it's slightly easier to deal with than the film list all my film count will do is return the number of records affected by this select statement I can do that very very simply using the global variable attached row count so to do this I need to set that's film count so it's a lot like using variables you set the value of the output parameter you set it to be equal to the value you're interested in and in this case is the result of a global variable called @ at row count setting the value of the film list output parameter is a little bit more complicated so first of all need to build the list within the Select statement so in order to make this work I'm going to declare a variable in my store procedure in which I'll store the list of films and then transfer that value into the output parameter at the end of the procedure so I'm going to declare at films I can't think of a more sensible name than that and the data side will be varchar' max again and then I can set the initial value of this parameter sorry this variable to be equal to an empty string if I don't initialize my variable it retains a value of null to begin with and that's going to affect what I'm going to try to do here in the Select statement what I'm going to do is build my list by saying select at films equal to at films plus the film name plus a comma and a space as a literal string so what this will do is build a comma-separated list of each film name that is selected based on this where clause all I need to do now is transfer the result of this variable into my app a parameter now I'll do this at the end of the procedure after I've stored at the row count I can say set at Film list equal to at films and there we go we have two simple output parameters and each of their values have now been set now that I've created all of the output parameters that I want I can show you how to retrieve those in a calling procedure before I do that I'll need to quickly execute this script however to update my current still procedure I need to alter it so in order to do that I click execute and make sure that at the bottom of the screen is as commands completed successfully I can then return to a script that I created earlier on which will execute this door procedure and this was with the original input parameter now that's about a to further parameters to it if I try to execute it again he's not going to work my output parameters are both compulsory because I haven't made them optional so in order to use my upper parameters I need a couple of variables into which the outputs will go so I'm going to declare a variable called at names and that'll be varchar' Max and that's going to hold the comma-separated list of film names I'm also going to declare the variable called at count and that's going to be an int and that's going to hold the count of the films returned what I need to do then is head back to my execute statement or my exact statement and add in a way to retrieve values from each of my output parameters so I have an output parameter called at film list so remember in my store procedure the film list is the one that retrieves the list of film names and I'm going to set the result of that parameter to be equal to the name of the variable into which I wish to pass the result and I also need to specify the Dustin as an output the same approach for my my count my film count if I say that's film count which is again the name of the output parameter and I want to make that equal to the name of the variable that I'm using in this procedure and that's going to be declared as output as well at this point I should be able to execute this script and I'll see rather than an error message commands completed successfully so I know now that I'm successfully retrieving values from these two output parameters all I need to do now is create some way to display them so let's do that with a simple select statement let's say select at count and I'm going to give that an alias as well that's going to be as number of films and then a comma and I can also say at names and I'll give that an alias list of films so when I finally execute the procedure one more time I should see not only the fact that my procedure works but also values of output to these two variables so far in this video we've seen how to use output parameters to return values from store procedures a couple of features that I haven't explicitly mentioned but are worthwhile noting about output parameters first of all you can have as many output parameters as you like in your store procedure your procedure can return as many values as you want the second feature is that you can also use any data type for an output parameter so you can use text numbers dates etc and there is one further way B can return a value from a stored procedure using a feature called a return value the they are slightly more simple to use them output parameters but they have two disadvantages first of all you can only have one return value in a single stored procedure and second of all the data type of that return value must be a number but with those restrictions in mind I'd like to quickly show you how you can use a return value to get an output from a store procedure to show you how return bodies work I've gone back to a previous version of my films in year procedure so I've taken away the output parameters and I've restored the Select statement to simply selecting a list of films made in the year that will provide in our calling procedure what I'd also like my procedure to do is return the count of the films that are affected by this select statement so to do that I'm going to add a return statement after the Select statement I'm going to return at at row count you can use the return statement to return any number that you like it could be an explicit value it could be the result of another expression as long as it's a number your stored procedure can return it so what I need to do now is execute this procedure to alter and update my changes the commands completed successfully and then I can think of a way to execute it to retrieve the value that is going to pass out so to get the result or the return value of a store procedure I need to execute it in a calling procedure here we go I'm executing films in year passing in the value 2000 to the year parameter now in a similar way to using output parameters when you want to get the return value of a store procedure you need to declare a variable usually to hold the result so I'm going to declare add count which will be an integer now the next bit of syntax is slightly odd in the execute statement we're going to exec the name of the variable that I've just created and make that equal to the result of this store procedure all I need to do now is display that information somewhere and I can do that with maybe a simple select statement so I'm going to say select add count and I'm going to give that an alias as number of films let's say and now again so if I execute this calling procedure I should see two sets of results this time not just the list of film names but also the result of my return statements as well if you've enjoyed this training video you can find many more online training resources at www.weiu.net

Channel: WiseOwlTutorials

Views: 99,822

Rating: 4.9071565 out of 5

Keywords: sql, output parameter, stored procedure, return, output, wise owl

Id: GvRv4V-AK70

Length: 12min 15sec (735 seconds)

Published: Wed Nov 28 2012