Looping Constructs in SQL Server T-SQL

SQL Server’s Transact-SQL (T-SQL) provides several ways to execute a block of code repeatedly. While traditional FOR loops found in many programming languages are not directly supported, T-SQL offers alternative looping constructs to achieve similar functionality. This tutorial will explore these options, providing examples to illustrate their usage.

The `WHILE` Loop

The WHILE loop is the primary mechanism for iterative execution in T-SQL. It continues executing a block of code as long as a specified boolean condition remains true.

Syntax:

WHILE boolean_expression
BEGIN
    -- Code to be executed repeatedly
END

Example:

The following example demonstrates a WHILE loop that prints numbers from 1 to 5:

DECLARE @i INT = 1;

WHILE @i <= 5
BEGIN
    PRINT @i;
    SET @i = @i + 1;
END;

In this example, the @i variable is initialized to 1. The loop continues as long as @i is less than or equal to 5. Inside the loop, the current value of @i is printed, and then @i is incremented by 1. This process repeats until @i becomes 6, at which point the loop terminates.

Simulating `FOR` Loops with `WHILE`

Because a dedicated FOR loop doesn’t exist, you can achieve similar behavior using a WHILE loop and a counter variable, as demonstrated above. This is the most common approach for iterative tasks in T-SQL.

The `DO..WHILE` Loop

The DO..WHILE loop executes a block of code at least once and then continues to execute the block as long as the specified condition is true. It’s similar to a DO...WHILE loop in other languages.

Syntax:

DO
BEGIN
    -- Code to be executed
END
WHILE boolean_expression;

Example:

DECLARE @X INT = 1;

WAY:
BEGIN
    PRINT @X;
    SET @X += 1;
END
WHILE @X <= 10;

In this example, the code block within DO is executed first, printing the value of @X. Then, the WHILE condition is checked. If @X is less than or equal to 10, the loop returns to the DO block and repeats. This continues until @X becomes greater than 10.

The `REPEAT..UNTIL` Loop

The REPEAT..UNTIL loop, also sometimes written as REPEAT...WHILE NOT condition, executes a block of code repeatedly until a specified condition becomes true.

Syntax:

WAY:
BEGIN
  -- Code to be executed
END
WHILE NOT boolean_expression;

Or:

REPEAT
  -- Code to be executed
UNTIL boolean_expression

Example:

DECLARE @X INT = 1;

WAY:
BEGIN
    PRINT @X;
    SET @X += 1;
END
WHILE NOT (@X > 10);

This example operates similarly to the DO..WHILE and REPEAT...UNTIL examples. The code block is executed, and the condition @X > 10 is evaluated. The loop continues as long as this condition is false. Once @X becomes greater than 10, the loop terminates.

Looping Through Result Sets (Using `OFFSET FETCH`)

When working with data in tables, you might need to loop through each row of a result set. While traditional loops can be used, the OFFSET FETCH clause provides a more efficient approach.

Example:

DECLARE @i INT = 0;
DECLARE @count INT;

SELECT @count = COUNT(*) FROM YourTable;

WHILE @i <= @count
BEGIN
    SELECT *
    FROM YourTable
    ORDER BY YourColumn  -- Specify an ordering column
    OFFSET @i ROWS
    FETCH NEXT 1 ROWS ONLY;

    SET @i = @i + 1;
END

In this example, @count stores the total number of rows in YourTable. The loop iterates from 0 to @count, fetching one row at a time using OFFSET FETCH. OFFSET @i ROWS skips the first @i rows, and FETCH NEXT 1 ROWS ONLY retrieves only the next row. Remember to specify an ORDER BY clause to ensure consistent results.