T-SQL WHILE Statement

The WHILE statement in Transact-SQL (T-SQL) is used to execute a set of Transact-SQL statements repeatedly as long as a specified condition is true.

Syntax

WHILE boolean_expression
    sql_statements

Explanation

• boolean_expression: An expression that evaluates to TRUE or FALSE. The loop continues as long as this expression is TRUE.
• sql_statements: One or more Transact-SQL statements that will be executed repeatedly.

How it Works

The WHILE loop first evaluates the boolean_expression. If the expression evaluates to TRUE, the sql_statements within the loop are executed. After the statements are executed, the boolean_expression is evaluated again. This process repeats until the boolean_expression evaluates to FALSE. Once the expression is FALSE, the loop terminates, and execution continues with the statement following the WHILE loop.

Key Concepts and Control Flow

• Looping Condition: The core of the WHILE loop is the condition that determines its continuation. Ensure this condition will eventually become FALSE to avoid infinite loops.
• BREAK Statement: Use BREAK to exit the WHILE loop prematurely, regardless of the loop's condition.
• CONTINUE Statement: Use CONTINUE to skip the rest of the current iteration of the loop and proceed to the next evaluation of the boolean_expression.

Example Usage

Basic Example: Counting

This example demonstrates a simple counter that increments until it reaches a certain value.

DECLARE @Counter INT;
SET @Counter = 1;

WHILE @Counter <= 5
BEGIN
    PRINT 'Counter is: ' + CAST(@Counter AS VARCHAR(10));
    SET @Counter = @Counter + 1;
END;
PRINT 'Loop finished.';

Example with BREAK

This example shows how to use BREAK to exit a loop early based on a specific condition.

DECLARE @Value INT;
SET @Value = 10;

WHILE 1 = 1 -- Infinite loop, but we'll break out
BEGIN
    PRINT 'Current value: ' + CAST(@Value AS VARCHAR(10));
    SET @Value = @Value - 1;

    IF @Value < 5
    BEGIN
        PRINT 'Value is less than 5, breaking loop.';
        BREAK; -- Exit the WHILE loop
    END
END;
PRINT 'Loop exited using BREAK.';

Example with CONTINUE

This example illustrates using CONTINUE to skip processing for certain loop iterations.

DECLARE @Number INT;
SET @Number = 0;

WHILE @Number < 10
BEGIN
    SET @Number = @Number + 1;

    IF @Number % 2 = 0 -- If the number is even
    BEGIN
        PRINT 'Skipping even number: ' + CAST(@Number AS VARCHAR(10));
        CONTINUE; -- Skip to the next iteration
    END

    PRINT 'Processing odd number: ' + CAST(@Number AS VARCHAR(10));
END;
PRINT 'Loop finished.';

Important Considerations

• Infinite Loops: Always ensure that the condition in your WHILE loop will eventually become FALSE. An infinite loop can freeze your SQL Server instance.
• Performance: For large datasets or complex operations, consider alternative set-based SQL operations, which are often more efficient than row-by-row processing in loops.
• Readability: Use meaningful variable names and clear logic to make your loops easy to understand.

The WHILE statement is a powerful tool for procedural logic within T-SQL. Use it judiciously for tasks that require iterative execution.