Stored Procedure Programming Concepts - While Loops | Essential SQL

In this lesson, we're going to learn about While Loops. So, what is a While Loop? Well, While Loop is set up using a While statement. While statements are used to repeatedly execute a block of SQL statements. That's where the Loop phrase comes from because we're kind of going around and around in a loop as we execute the statements.

The block is repeatedly executed while the statement's condition is true. That's kind of how the semantics works. While the condition of the While statement is true we will execute a block of code. Here's an example of a While Loop where we're counting to 10. In this example we have a variable where we're declaring it as an integer. We're starting at a one. We have a While statement and it's saying WHILE, our variable I is less than or equal to 10.

So that's where we're counting the 10. We get to 10, we're going to stop. In the meantime we have a loop and we're going to execute this block of code within the begin and end statement. First of all we're going to print the code. Then we're going to increment our variable by one. So we're going to get, if I is one we're going to add one to it and it's going to become two. And then we're going to go back to the top.

We're going to test I. Is I less than 10? Sure is. Print out the value, increment the value. Keep going around and around. Before you know it we've added one to I enough times that I is going to be 11 and 11 is greater than 10. We will not execute any more code within the beginning and end code block here and we will exit the loop.

If you find yourself repeating statements, especially those that fall into a pattern, then there's a possibility you can use a WHILE statement to save some typing and make your programming more fun to write. To go back and look at our example here, you can imagine having to type out this set equals I equals I plus one, 10 different times, it would be become kind of boring.

You wouldn't do that probably but you get the point that if you have a lot of code that's repeating, there's a pattern to it, you're probably going to start going, "I bet you I can do a loop and this will be a little more fun to write than just typing all this stuff out." So when you start seeing a pattern to your code, start thinking about loops especially WHILE loops. So let's go look at some examples of how we can use WHILE loops.