Loops
Looping structures allow a single command, or a group of statements, to be executed repeatedly. When using the T-SQL WHILE loop, a Boolean condition is checked every time the code within the loop is about to start. If the condition is true, the loop is executed. If not, control passes to the statement following the loop.
WHILE loops are commonly used with cursors, which are described in the next article in this tutorial, to process a set of data one row at a time. In this article we will concentrate on the structure of the loop with simple examples that can be executed directly without accessing a database.
Using WHILE Loops
The syntax for creating a while loop is as follows:
WHILE condition statement
This syntax is the same as the basic syntax for the IF statement that we saw in the previous article. The condition element specifies a value, variable or expression that evaluates as either true or false. If the value is true, the statement part of the command is executed. Once completed, the condition is checked again and the process continues until the condition is false. NB: The statement element can contain multiple commands if they appear between BEGIN and END commands.
We can demonstrate a loop by executing a script in SQL Server Management Studio or your preferred environment for running T-SQL. The following script declares a variable and initialises its value to zero. The loop's condition specifies that the contained statements will be executed whilst the variable's value is less than or equal to ten. Within the loop, the variable's value is incremented and printed. The end result is that the integers between one and ten are outputted.
DECLARE @Iteration INT
SET @Iteration = 1
WHILE @Iteration <= 10
BEGIN
PRINT @Iteration
SET @Iteration = @Iteration + 1
END
Exiting a Loop Explicitly
Sometimes you will wish to terminate a loop even when the condition part is true. This can be achieved using the BREAK command. This command specifies that the control should pass to the command immediately following the current loop.
In the following sample script, the loop is calculating the squares of the values between one and ninety-nine. The IF statement checks to see if the result of the square is greater than one thousand. On the first time that this happens, the loop is terminated, even though the @ToSquare variable is still less than one hundred.
DECLARE @ToSquare INT
DECLARE @Square INT
SET @ToSquare = 0
WHILE @ToSquare < 100
BEGIN
SET @ToSquare = @ToSquare + 1
SET @Square = @ToSquare * @ToSquare
IF @Square > 1000
BREAK
PRINT @Square
END
Restarting a Loop
If you wish to terminate a single iteration of a loop you can use the CONTINUE command. This command immediately stops the current iteration and rechecks the loop's condition. If the condition is still true, the loop is restarted. In the next example the CONTINUE statement and BREAK statement are combined so that the squares that are between one hundred and one thousand are outputted.
DECLARE @ToSquare INT
DECLARE @Square INT
SET @ToSquare = 0
WHILE @ToSquare < 100
BEGIN
SET @ToSquare = @ToSquare + 1
SET @Square = @ToSquare * @ToSquare
IF @Square < 100
CONTINUE
IF @Square > 1000
BREAK
PRINT @Square
END
Nesting
Loops can be nested to provide more complex looping structures. When nesting loops, BREAK and CONTINUE commands affect only the loop that they appear within. ie. when breaking out of an inner loop, control passes to the next statement after the inner loop, which will be within the outer loop.
In the following example one loop is nested within another to output the multiplication tables for the numbers one to ten.
DECLARE @Val1 INT
DECLARE @Val2 INT
SET @Val1 = 1
WHILE @Val1 <= 10
BEGIN
SET @Val2 = 1
WHILE @Val2 <= 10
BEGIN
PRINT CONVERT(VARCHAR, @Val1) + ' * ' + CONVERT(VARCHAR, @Val2)
+ ' = ' + CONVERT(VARCHAR, @Val1 * @Val2)
SET @Val2 = @Val2 + 1
END
SET @Val1 = @Val1 + 1
END
3 January 2010