SQL While sets a condition for the repeated execution of an SQL statement or statement block. The SQL statements are executed repeatedly as long as the specified condition is return TRUE. The WHILE loop can be controlled from inside the loop with the CONTINUE,BREAK and GOTO keywords.
BREAK statement will exit you from the currently processing WHILE loop.
GOTO statement will break out of all WHILE loops, no matter how many nested WHILE statements.
CONTINUE statement will skips executing the rest of the statements between the CONTINUE statement and the END statement of the current loop and starts executing at the first line of the current WHILE loop.
1) Example of WHILE Loop
DECLARE @var INT
SET @var = 1
WHILE (@var <=5)
BEGIN
PRINT @var
SET @var = @var + 1
END
GO
ResultSet:
1
2
3
4
5
2) Example of WHILE Loop with BREAK keyword
DECLARE @var INT
SET @var = 1
WHILE (@var <=5)
BEGIN
PRINT @var
SET @var = @var + 1
IF @var = 4
BREAK;
END
GO
ResultSet:
1
2
3
3) Example of WHILE Loop with CONTINUE and BREAK keywords
DECLARE @var INT
SET @var = 1
WHILE (@var <=5)
BEGIN
PRINT @var
SET @var = @var + 1
CONTINUE;
IF @var = 4 — This will never executed
BREAK;
END
GO
ResultSet:
1
2
3
4
5
