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.
WHILE Syntax
WHILE Boolean_expression
{ Sql Statement Block }
Below is simple example of WHILE Statement
DECLARE @counter INT
SET @counter = 0
WHILE @counter <>
BEGIN
SET @counter = @counter + 1
PRINT 'The counter : ' + CAST(@counter AS CHAR)
END
Value :
The counter : 1
The counter : 2
The counter : 3
The counter : 4
The counter : 5
Below is example of WHILE Statement with CONTINUE and BREAK
- It show you the using of Continue and Break in WHILE Statement and the IF...ELSE Statement.
DECLARE @Counter INT
SET @Counter = 0
WHILE @Counter <>
BEGIN
SET @Counter = @Counter + 1
IF @Counter <>
PRINT 'The counter : ' + CAST(@Counter AS CHAR)
ELSE IF @Counter > 3 AND @Counter <>
BEGIN
CONTINUE
PRINT 'No Counter Here'
END
ELSE IF @Counter > 13 AND @Counter <>
BREAK
ELSE
PRINT 'The counter : ' + CAST(@Counter AS CHAR)
END
Value :
The counter : 1
The counter : 2
The counter : 3
The counter : 10
The counter : 11
The counter : 12
The counter : 13
Below is example of WHILE Statement with CONTINUE, BREAK and GOTO
- It show you the using of Continue, Break and Goto in WHILE Statement and the IF...ELSE Statement.
DECLARE @N1 INT
DECLARE @N2 INT
SET @N1 = 0
SET @N2 = 0
WHILE @N1 <>
BEGIN
SET @N1 = @N1 + 1
WHILE @N2 <>
BEGIN
SET @N2 = @N2 + 1
IF @N2 = 3 and @N1 = 1
GOTO BREAK_OUT
ELSE
PRINT 'Value N1 is ' + CAST(@N1 AS CHAR(1)) + ' Value N2 is ' + CAST(@N2 AS CHAR(1)) END
SET @N2 = 0
END
BREAK_OUT:
Value :
Value N1 is 1 Value N2 is 1
Value N1 is 1 Value N2 is 2
