T-SQL Loops

Understanding Loops in T-SQL

Loops are fundamental control flow structures that allow you to execute a block of code repeatedly. T-SQL offers several ways to implement loops, each suited for different scenarios.

WHILE Loop

The WHILE loop executes a statement or a block of statements as long as a specified condition is true. It's the most common type of loop in T-SQL.

Syntax:

WHILE condition
BEGIN
    -- Statements to execute
END

Example:

Incrementing a counter:

DECLARE @Counter INT = 1;

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

BREAK and CONTINUE

These statements are used within loops to alter their flow.

BREAK:

Exits the loop immediately, regardless of the loop condition.

DECLARE @i INT = 0;

WHILE @i < 10
BEGIN
    SET @i = @i + 1;
    IF @i = 5
        BREAK; -- Exit loop when @i reaches 5
    PRINT @i;
END;

CONTINUE:

Skips the rest of the current iteration and proceeds to the next one.

DECLARE @j INT = 0;

WHILE @j < 5
BEGIN
    SET @j = @j + 1;
    IF @j % 2 = 0
        CONTINUE; -- Skip even numbers
    PRINT 'Odd number: ' + CAST(@j AS VARCHAR(10));
END;

LOOP Considerations

While loops are powerful, it's important to use them judiciously. Infinite loops can cause performance issues or even block your database.

Potential Pitfalls:

  • Infinite Loops: Ensure your loop condition will eventually become false.
  • Performance: Complex operations within loops can be slow. Consider set-based operations where possible.
  • Readability: Complex nested loops can be hard to understand and maintain.

Alternatives:

For many data manipulation tasks, T-SQL's set-based operations (e.g., UPDATE, DELETE, INSERT with SELECT) are significantly more efficient than cursors or row-by-row processing within loops.