SQL Stored Procedures: Loops

To understand how conditional logic works in stored procedures checkout my previous post. Now let us look at how the loops work in SQL.

What are loops in SQL?

Loops are similar to other languages, a logical block of code that keeps executing repeatedly until the provided condition is met.

1. WHILE Loop Syntax

DECALRE @ConditionalVariable INT
SET @ConditionalVariable = 10
WHILE @ConditionalVariable < 100
    BEGIN
        -- Do Something
        SET @ConditionalVariable = @ConditionalVariable+1
    END

In the above example we show functioning of a WHILE loop in SQL. It has a conditional constraint after the WHILE keyword. Then the code block begins. Again BEGIN/END statements scope the code to be executed within the loop.

The SET statement inside the BEGIN/END block increments the @CondtitionalVariable so that the loop condition is met eventually and the loop doesn’t become an infinite loop. The following loop is an example of an infinite loop.

DECALRE @ConditionalVariable INT
SET @ConditionalVariable = 10
WHILE @ConditionalVariable < 100
    BEGIN
        -- Do Something
        
    END

2. Early Breaking

DECALRE @ConditionalVariable INT
SET @ConditionalVariable = 10
WHILE @ConditionalVariable < 100
    BEGIN
        -- Do Something
        IF @ConditionalVariable = 90
            BEGIN
                BREAK
            END
        SET @ConditionalVariable = @ConditionalVariable+1
    END

For breaking the loop early we use BREAK statement and it is generally enclosed in some form of conditional logic. So if that condition is met we want the loop to stop executing.

This lesson is another short one. Next time we will see the usefulness of loops by combining them with cursors. The last lesson on conditional logic can be found here.