See Also: Main_Page - Transact SQL Language Elements - Loops & Conditional Processing
Looping is a repeated execution of the same commands until a certain condition evaluates to a different Boolean value (TRUE or FALSE). The only looping structure supported by SQL Server is WHILE loops.
The while loops continue execution until the specified condition is no longer true OR until the loop is intentionally interrupted by a BREAK statement. If BREAK is used then processing passes to the next valid T-SQL statement outside the loop. The CONTINUE statement can be used to return execution to the top of the loop skipping any statements between the CONTINUE and the end of the loop.
The statements inside the WHILE loop need to be enclosed in the BEGIN â€¦ END delimiters. The Boolean condition to be evaluated by the WHILE statement can be a variable value, comparison of variables or a SELECT statement. For instance, the following loop will continue executing while a variable is less than 5:
DECLARE @counter INT SELECT @counter = 0 WHILE @counter < 5 BEGIN SELECT 'the variable @counter is ' + CAST(@counter AS VARCHAR(1)) SELECT @counter = @counter + 1 END
In the next example two variable values are compared. The loop will continue until two of these variables have the same value:
DECLARE @counter INT, @counter1 INT SELECT @counter = 0, @counter1 = 2 WHILE @counter <> @counter1 BEGIN SELECT 'the variable @counter is less than @counter1' SELECT @counter = @counter + 1 ENDThe next example shows how to use a SELECT statement as a loop condition:
CREATE TABLE #temp (my_id INT NOT NULL identity(1,1), my_name VARCHAR(20) NULL) WHILE (SELECT count(*) FROM #temp)<10 BEGIN INSERT #temp VALUES ('name_variable') END
Sometimes you have to write a loop that will execute an unknown number of times before it needs to stop executing. This can be accomplished by writing an infinite loop that contains a BREAK statement after the work has been accomplished, as in the following example:
Although 10 will always equal 10 (an infinite loop), the BREAK statement will unconditionally stop execution of the program right after the first record has been inserted into the temporary table.
The while loops can be nested. You need to exercise great care when working with nested loops since the innermost BREAK statement will only get out of the current loop and get into the next outermost loop. It will not completely exit the topmost loop. Any valid T-SQL statements following the innermost loop are executed before the next outermost loop restarts.
If you wish to go to the beginning of the loop use the CONTINUE statement. This will cause the loop to reexamine the WHILE condition. If the condition still evaluates to true the loop will re-execute, otherwise the processing will continue at the statement immediately following the loop.
Occasionally you might wish to terminate the loop before the WHILE condition evaluates to false. One way to do so would be using GOTO. You can use GOTO to jump to any labeled portion of code within or outside of the loop. If you wish to completely get out of the program execution, use the RETURN statement. RETURN will ultimately end the whole program, not just the loop. Finally, you can use multiple BREAK statements to get out of all levels of looping.
The following example demonstrates the usage of nested loops. This code returns the list of the stores that have sold 50 or more items in each year of operations, incrementing the minimum number of items by 10. If the program finds that none of the stores sold more than 50 items during a particular year it will exit from the inner loop and check the following year: