WHILE statement is used together with a condition and forms a loop. Basically WHILE sets a condition for repeated execution within itself of an SQL statement or a block of statements. As long as the specified condition is true, the SQL statements are executed repeatedly. Inside the loop created with the WHILE statement, the keywords BREAK and CONTINUE can be used to control the execution of SQL statements.

Syntax

WHILE Boolean_expression
{ sql_statement | statement_block | BREAK | CONTINUE }

WHILE example

The example below shows how to use the WHILE statement. The WHILE condition is set, the maximum number of students should be less than or equal to 10 and the loop with SQL instructions begins. The names of the first 3 students are displayed, then when the @count = 3 condition is met, BREAK exits the loop, otherwise CONTINUE runs the loop.

USE model
GO
DECLARE @count int, @name varchar(100)
SET @count = 0
WHILE (SELECT max(id) FROM Students) <= 10
BEGIN
SET @count = @count + 1
SELECT @name=name FROM Students WHERE id=@count;
PRINT @name
IF @count = 3
BREAK
ELSE
CONTINUE
END;
GO