BlackWaspTM

This web site uses cookies. By using the site you accept the cookie policy.This message is for compliance with the UK ICO law.

SQL Server
SQL 2005+

SQL Server WHILE Loops

The fifty-fifth part of the SQL Server Programming Fundamentals tutorial examines Transact-SQL's (T-SQL) WHILE statement. This powerful flow-of-control command allows the creation of looping structures in scripts and stored procedures.

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