SQL Server Programming Part 6 - WHILE Loops

welcome to this YSL tutorial in this video we're going to teach you how to use while loops in Microsoft sequel server what we'll cover in this session are all the things you need to know in order to work with while loops in SQL we'll start with the basic syntax of the while statement the move on and show you how you can use a select statement inside a loop will teach you how you can end a loop prematurely using the break statement and also what to do if you get stuck in the dreaded endless loop we'll finish the video off with a quick look at how you use cursors and how you use a while loop to step through a set of records so let's get started in programming a loop allows you to repeatedly execute a set of instructions many times very quickly now lots of programming languages have several different types of loops available to them but in SQL there's only one type of loop and it's referred to as a while loop so to demonstrate the basic syntax we've got a basic procedure here which has a single integer variable which I've initialized to a value of 1 and we're going to use that to begin a while loop so to start a while loop you write in the word while and then you need to provide some kind of logical test so my logical tests can say while at counter is less than or equal to 10 below this line I'm going to start a begin and end block and then within these two lines I can write out a set of instructions and the idea is each time this line executes s-- while at counter is less than or equal to 10 as long as that condition is true will enter the beginning end block and execute all the instructions and then return to the wild statement to check if this condition is still true and that process will repeat all the time while this condition is true I'm going to do something very simple I'm just going to print the value of my counter variable and then I'm going to increment my counter variable by 1 so I'm going to say setup counter equals that's counter plus 1 when I execute this very small procedure all I should end up with is a list of the numbers 1 through 10 so it's not very exciting but it gives you the idea of the basic loop syntax now let's see if we can make our loop a little bit more useful what I'd like to do is use the value of my counter variable to provide the criteria in the where Clause of a select statement what I'm going to do is add an extra variable at the top here I'm going to declare out max oscar's which would be another integer what I'm going to do is I'm going to work out what is the highest number of Oscar wins of any film in the film table of my movies database so I'm going to do that by saying set that's max Oscar's equals and then in a set of round brackets select max film Oscar wins from TBL film so this variable now will store the highest number of Oscar wins of any film in that table I'm going to set my at counter variable I'm going to initialize it to 0 and the reason I'm doing that is I know that some films have 1 0 Oscars and what I can do now is modify the while statement so I'm going to do this while add counter is less than or equal to not an explicit value of 10 but the value of my Max Oscars variable what I'd like to do now is count the number of films which have won each number of Oscars stalling the counter variable so to make that work I'm going to declare another variable which is going to be called apt num films with as an integer again now I need to modify the lines in the begin and end block within the loop there are three things that I need to do first I need to count the number of films with this many Oscar wins then I want to print out a sensible message which tells me how many films have blown that number of ask and then I'd like to as I am already increment value of my counter before returning to the top of the loop again so to count the number of films which have won the number of Oscars I'm interested in I'm going to add a new line which sets the none films variable equal to the result of a select statement I'm gonna add this sir the next line I need to be enclosed within a set of round brackets I'm going to simply select account of the asterisk from TBL film where film Oscar wins is equal to the value stored in my counter variable so it goes for me the number of films winning that's a number of Oscars the next step is to edit the print statement to print slightly more sensible message so for example if I had a hundred films that have 1-0 Oscars that's pretty much what I want my message to say 100 films have won zero Oscars so to modify my prints diamonds I'm going to take away was there already I need to join together numerical values with literal text and to do that I need to make sure that I've cast the numbers as some kind of text data type so I'm going to cast at num films as varchar' 3 and then I'm going to join that together with the literal text films have one I'm going to join that together again now with the value of my counter variable I'm going to do this on the next line but I need to use a cast cast function again and I'm going to cast out counter as varchar' two and then add in the final bit of information the final bit of literal text at Oscars and that will build a complete hopefully sensible looking sentence when the when that query is run the third thing I need to do is increment the value of my counter variable and that's happening all ready anyway so the way the whole loop will work counter will start at zero we'll check if counter is less than or equal to the maximum number of Oscar wins and as long as it is will count how many films have won zero Oscars but print our message and then increment our counter we'll check if the counter variable and gain is less than or equal to the maximum Oscars and as long as it is what counts how many films have won one Oscar print out the message in the counter and so on so let's see the results of that by executing the procedure and those are the results so you see a nice sensible message about each value of Oscar wins from zero through two eleven again it's not the most useful or exciting example in the entire world but it's a neat demonstration of how loops work how the logic of a loop works we could have got similar results using a group by Clause within a select statement of course although we would have missed out these two lines where there are no films winning nine or ten Oscars our group by clause wouldn't have included those sometimes in a loop you'll find that you want to stop executing it before you reach its natural end based on the while statement so for example and this this procedure let's imagine them if we ever found that there were zero films winning the number of Oscars we're testing for that we want to stop executing our loop and just go with the final set of outputs so we've already got so what we're going to do is add an if statement to check if the number of films is ever zero stop executing the loop so I can do that by adding a couple of blank lines here and starts with an if statement now we've written a we've produced a video and if statements previously if you're not familiar with them we're just going to show you the absolute basics here we're going to check if adds num films equals zero the statement that only ters provide now is simply the word break the break statement will immediately stop executing the the while loop and will return to the next line after the end block so if there were more instructions below the end block these would be executed but in my example here this will signal the end of the entire procedure so if I execute the procedure again now we should find that our list of messages is cut off that's the value of eight Oscars as we've already seen nil films up with nine Oscars so that's where my loop stops executing I want to make one quick mention of a common issue that people often Albert loop so that's getting stuck in the dreaded endless loop I've done to myself on many an occasion what I'm going to do is take away my if statement from the previous little example I'm going to comment out the the set counter to be one more than it previously was line as well so my counter never increments which means it always stays at zero which means that every time I execute a loop counter will always be less than max Oscars so this condition will always be true which means when I execute the procedure it is executing but that will never ever ever end so how do you stop that from happening well this little tiny red square on the on the toolbar if I cancel that inquiry by clicking that or I can also press alt and brake on the keyboard as you can see in the tooltip there that will stop execution and you can see even those few seconds I was talking how many times that that loop has been executed so it's very easy to trap to fall into the endless loop and you have to make sure that your condition can always return false to avoid that just to end this video I'd like to show you one more fairly common use for while loops in SQL and that's to use them with cursors a cursor is a way to step through a set of records one row at a time there's almost like having a pointer in each record in a data set and moving through one step at a time so what we've done instead of the basic procedure we required to create a cursor so I've got two variables here which I'm going to use to read in the values for each row for each record then I've declared my cursor which selects the film ID and film name from the film table I've opened the cursor and this line here and that just the first set of data into my variables what I'd like to do now is make the cursor step through each record in turn one by one until it reaches the end of the record set and the way that you do that is using a while loop and use the while loop to check the value of a global variable called at fetch status we're going to check if that is equal to zero that means that it's successfully selecting a new record so that's my logical test as a basic smart while loop I can add a begin and an end block now and work out what I'd like to do within the loop itself what I'd like to do is print some information about the name of the film I've selected into the cursor and also a list of characters in that film so I'm going to do that with a couple of print statements are going to say print characters in the film and I can join that together with the name of the film stored in my film and name a variable and then what I'm going to do is I'm going to select a specific field called casts character name from a table called TBL cast I'm going to do that where the cast film ID is equal to the value stored in my other variable which is at a film ID what that's going to do is show me a list of all the characters who played a role in that film now bearing in mind what we've just shown you about endless loops I have to make sure that my cursor moves to the next record and the way that I do that is actually by repeating the line which fetched the first record from the cursor it's going to copy that line plunk it in within my while loop and that's the loop itself created there's a couple of actual little lines I'm going to write in these are related to the cursor itself which I'm not going to explain in too much details I'm going to close my film cursor hopefully that's reasonably obvious I'm also going to choose to deallocate film cursor as well these will be explained in a lot more detail when we produce our video on cursors all that let's do now is show the results of this procedure and I'm going to quickly change where the results come out rather than resulting to the grid I'm going to change them to text just because it's slightly easier to see and if I execute the procedure I ought to end up with a nice long list of all the characters in various films it's nice that you can see how slowly cursors run that's gonna be a feature of the video will we'll cover on cursors later but as you can see we pick the first film a list of the characters in that film and so on and so on and so on if you've enjoyed this training video you can find many more online training resources at ww-why Zeljko UK

Channel: WiseOwlTutorials

Views: 108,637

Rating: 4.9242902 out of 5

Keywords: SQL (Programming Language), while, loop, break, cursor, wise owl

Id: gWnhFn0ugoM

Length: 14min 48sec (888 seconds)

Published: Wed Dec 05 2012