WHILE loops

A WHILE loop is similar to an IF statement, but after executing the statement, it goes back up to the top and starts over. It has the same basic structure as an IF statement without the option of an ELSE clause:

 while <expression>      <statement>

A WHILE loop keeps executing the statement until the expression becomes false. If the expression never becomes false, you have a problem, also known as an infinite loop . So, the statement needs to do something useful, and it also needs to change one of the values involved in the expression so that eventually, when the time is right, the expression will evaluate to false and the looping will end.

A classic looping structure has three steps. The initialization step sets up the variables and populates them to initial values. The test step evaluates the expression and determines whether the loop should be repeated. The incrementation step performs useful work, usually changes the expression somehow, and returns to the test step. Just to help you see this structure, the upcoming examples point out where each of these steps occur in the while statement.

A simple WHILE loop that counts to 100 would look like this:

 DECLARE @i int SET @i = 1 --initialization WHILE @i <= 100 --test BEGIN       PRINT @i       SET @i = @i + 1 --incrementation END

So, what happens if you leave out one of the steps? If you don't initialize the loop, then the test step becomes ( NULL <= 100 ), which evaluates to false for the purposes of the loop, so the loop never executes. If you leave out the test, perhaps by putting in a test that always evaluates to false ( 1=0 ) or true ( 1=1 ), then you either have a loop that never executes or a loop that executes forever. If you leave out the intermediate step, which is the most common error of all, you end up with a loop that does the same thing over and over, in this case, printing the number 1.

Two special keywords can be used to change how a WHILE loop operates. The CONTINUE keyword short-circuits the statement being executed and immediately goes back up to the loop test, ignoring the rest of the statement block. The BREAK keyword exits the while loop and starts executing the statement after the end of the statement block. Here's an example of the BREAK keyword:

 DECLARE @i int SET @i = 1 --initialization WHILE @i <= 100 --test BEGIN       PRINT @i       SET @i = @i + 1 --incrementation       IF @i = 42             break END

This causes the loop to stop counting when it reaches the number 42, but after it has printed 41. The number 42 will not be printed. Here's an example of using a CONTINUE keyword:

 DECLARE @i int SET @i = 1 --initialization WHILE @i <= 100 --test BEGIN       PRINT @i       SET @i = @i + 1 --incrementation       IF @i = 42       BEGIN           SET @i = @i + 1           continue      END END

This skips printing the number 42 and goes straight on to printing 43 and up to 100. Why does it increment the variable before using CONTINUE ? If it didn't, then the statement would actually print the number 42 and continue along, just like the very first WHILE loop.

Although loops are great, how often do you need to count things in T-SQL? It would be great if you could use this structure to work on one row from a table and then loop to perform the same operation with data from the next row. How would you do that? Read on.