SQLConfiguration SQLConfiguration


SQL-Query Basics  «Prev 

Using While Statement in SQL-Server

This SQL Server tutorial explains how to use the WHILE LOOP in SQL Server (Transact-SQL) with syntax and examples.
In SQL Server, you use a WHILE LOOP when you are not sure how many times you will execute the loop body and the loop body may not execute even once.

WHILE condition
BEGIN
{...statements...}
END;

Parameters or Arguments

  1. condition: The condition is test each pass through the loop. If condition evaluates to TRUE, the loop body is executed. If condition evaluates to FALSE, the loop is terminated.
  2. statements: The statements of code to execute each pass through the loop.
Pay attention to the following facts:
  1. You would use a WHILE LOOP statement when you are unsure of how many times you want the loop body to execute.
  2. Since the WHILE condition is evaluated before entering the loop, it is possible that the loop body may not execute even once.
  3. See also the BREAK statement to exit from the WHILE LOOP early.
  4. See also the CONTINUE statement to restart the WHILE LOOP from the beginning.

@LoopCount is declared as an integer variable that will be used in the rest of the statement

@LoopCount is initialized to a value of 0

The WHILE loop is set up to be active as long as the @LoopCount variable is less than 10.

The BEGIN and END statement start and stop the loop

The @LoopCount variable is incremented by one, then tested. When @LoopCount equals 5, the BREAK statement is executed.

When the BREAK statement is executed, the value of @LoopCount will be tested again. Note that no BEGIN ... END construct is needed around the BREAK statement because it is a single statement

If @LoopCount = 5, a new loop is entered.

A static string is constructed and returned back to the client.

SQL Server is instructed to jump immediately to the label Five_label.

If @LoopCount did not equal 5.

The else condition returns a static string back to the client.

The value unconditionally exits from the entire procedure.

Placeholder: need to get correct text for this page

A string is constructed and returned back to the client, indicating that the code make it into the label.