WHILE Loops in T-SQL Stored Procedures

In this lesson, we’re going to learn about the While Loop. 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.

After watching this video you’ll be able to declare WHILE loops and understand how they are used in repeating patterns within stored procedures.  You’ll find out isn’t as hard as everyone makes them out to be and you’ll have one of the skills a junior DBA should know.

If you want to learn more about how to define WHILE loops, why not take my course Stored Procedures Unpacked?  Use coupon code BLOGPOST-20 to get it at a significant discount.

While Loops in T-SQL

In this lesson, we’re going to learn about the WHILE Loop. 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 WHILE 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.

If you want to learn more about how to create stored procedures, why not take my course Stored Procedures Unpacked?  Use coupon code BLOGPOST-20 to get it at a significant discount.Image

Other available programs and classes

The post WHILE Loops in T-SQL Stored Procedures appeared first on Essential SQL.