WHILE loops in procedures in SQL

Introduction

WHILE loops help repeat a set of actions in a procedure until a condition is no longer true. This saves time and avoids writing the same code many times.

When you want to process rows or data repeatedly until a condition changes.

When you need to perform a task multiple times but don't know the exact number in advance.

When you want to automate repetitive database tasks inside a procedure.

When you want to build a counter or accumulator inside a stored procedure.

When you want to loop through numbers or dates to insert or update data.

Syntax

SQL

WHILE condition
BEGIN
  -- statements to repeat
END

The condition is checked before each loop. If it is true, the loop runs.

Use BEGIN and END to group multiple statements inside the loop.

Examples

This loop prints numbers from 1 to 5 by increasing the counter each time.

SQL

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

This loop runs as long as there are tasks with status 'Pending'. It updates one task to 'Done' each time.

SQL

WHILE EXISTS (SELECT 1 FROM Tasks WHERE Status = 'Pending')
BEGIN
  -- process pending tasks
  UPDATE TOP (1) Tasks SET Status = 'Done' WHERE Status = 'Pending';
END

Sample Program

This procedure prints 'Loop number: 1', then 2, then 3 using a WHILE loop.

SQL

DECLARE @counter INT = 1;
WHILE @counter <= 3
BEGIN
  PRINT CONCAT('Loop number: ', @counter);
  SET @counter = @counter + 1;
END

Important Notes

Make sure the loop condition will eventually become false to avoid infinite loops.

You can use variables inside the loop to control how many times it runs.

WHILE loops are useful but sometimes set-based SQL queries are faster and simpler.

Summary

WHILE loops repeat code while a condition is true.

Use them inside procedures to automate repeated tasks.

Always update variables inside the loop to avoid endless repetition.