SQL While

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