A WHILE loop in SQL Server is used to execute a block of code repeatedly as long as a specific condition remains TRUE. It is the primary way to perform iterative logic in T-SQL.

1. Basic Syntax

The loop evaluates a condition before each iteration. If the condition is true, the code inside the BEGIN...END block runs.

DECLARE @Counter INT = 1;

WHILE @Counter <= 5
BEGIN
    PRINT 'Iteration Number: ' + CAST(@Counter AS VARCHAR);
    SET @Counter = @Counter + 1; -- Crucial: Increment to avoid infinite loop
END

2. Controlling the Loop: BREAK and CONTINUE

You can gain finer control over the loop using these two keywords:

  1. BREAK: Immediately exits the loop, regardless of the condition.
  2. CONTINUE: Skips the rest of the code in the current block and jumps back to the WHILE condition for the next check.

Example with BREAK:

DECLARE @Count INT = 1;

WHILE @Count <= 10
BEGIN
    IF @Count = 6
        BREAK; -- Stops the loop entirely when it hits 6
        
    PRINT 'Count is: ' + CAST(@Count AS VARCHAR);
    SET @Count = @Count + 1;
END

3. Practical Use: Processing Data Row-by-Row

While SQL is designed for “set-based” operations (processing all rows at once), sometimes you need to loop through a table. A common pattern is using a Table Variable and deleting rows as you process them.

DECLARE @UserIDs TABLE (ID INT);
INSERT INTO @UserIDs SELECT UserID FROM Users WHERE IsActive = 0;

DECLARE @CurrentID INT;

-- Loop as long as there are rows in the table variable
WHILE EXISTS (SELECT 1 FROM @UserIDs)
BEGIN
    SELECT TOP 1 @CurrentID = ID FROM @UserIDs;

    -- Perform an action for this specific ID
    EXEC DeactivateUserRecords @CurrentID;

    -- Remove the processed ID so the loop eventually ends
    DELETE FROM @UserIDs WHERE ID = @CurrentID;
END

4. Processing Rows with WHILE (Cursor Alternative)

While set-based operations (like UPDATE or INSERT INTO...SELECT) are faster, sometimes you need to process rows one by one. You can do this by using a Table Variable and deleting rows as you process them.

Example:

DECLARE @Items TABLE (ID INT);
INSERT INTO @Items VALUES (101), (102), (103);

DECLARE @CurrentID INT;

-- Loop as long as there are rows in the table variable
WHILE EXISTS (SELECT 1 FROM @Items)
BEGIN
    -- Pick the top ID
    SELECT TOP 1 @CurrentID = ID FROM @Items;

    -- Perform an action
    PRINT 'Processing Product ID: ' + CAST(@CurrentID AS VARCHAR);

    -- Remove the row so the loop eventually ends
    DELETE FROM @Items WHERE ID = @CurrentID;
END

5. Performance Best Practice

Warning: SQL Server is optimized for Set-Based logic. Before using a WHILE loop, ask yourself: “Can I do this with a single UPDATE, INSERT, or MERGE statement?” Loops are often significantly slower because they perform operations row-by-row (RBAR).

Use WHILE only when:

  • You are calling a stored procedure for every row.
  • You need to perform administrative tasks (like rebuilding indexes one by one).
  • The logic is too complex for a standard query.

Would you like me to show you how to use a WHILE loop with a TRY…CATCH block to handle errors during batch processing?

Categorized in:

Stored Procedures,