SQL Server WHILE loop sets a condition for the repeated execution of an SQL statement or statement block.
The statements are executed repeatedly until the specified condition result turn out to be false. The execution of statements in the WHILE loop can be controlled from inside the loop with the BREAK and CONTINUE keywords.
SYNTAX
WHILE Boolean_expression { sql_statement | statement_block | BREAK | CONTINUE }
Boolean_expression
Is an expression that returns TRUE or FALSE.
{sql_statement | statement_block}
Is any SQL statement or statement grouping as defined with a statement block. It is defined inside BEGIN and END block.
BREAK
is used in the SQL WHILE loop in order to exit the current iteration of the loop immediately when certain conditions occur.
CONTINUE
Causes the WHILE loop to restart, ignoring any statements after the CONTINUE keyword.
Lets look at an example of using while loop .
Following T-SQL Statement uses while loop an execute print statement five times until the variable value reaches till 5.
DECLARE @Seq INT SET @Seq=1 WHILE ( @Seq <= 5) BEGIN PRINT 'Row Number is = ' + CONVERT(VARCHAR,@Seq) SET @Seq = @Seq + 1 END
WHILE with BREAK Keyword
Following T-SQL statement uses the Break keyword with WHILE Statement.
It checks the value of the variable, and when the value is equal or greater than 3, then control is passed to IF…ELSE block and execute the BREAK statement and exit the current iteration of the loop immediately.
As you can see, it executes print statement only for three times and when variable value goes greater than 3.
DECLARE @Seq INT SET @Seq=1 WHILE ( @Seq <= 5) BEGIN PRINT 'Row Number is = ' + CONVERT(VARCHAR,@Seq) IF @Seq>=3 BEGIN BREAK END SET @Seq = @Seq + 1 END
WHILE LOOP WITH CONTINUE
CONTINUE statement is used in WHILE loop in order to stop the current iteration of the loop when certain conditions occur, and then it starts a new iteration from the beginning of the loop.
Suppose, that we want to skip the execution of print statement within while loop for variable value 2 and 4. In this case we can use the CONTINUE statement.
The following T-SQL Statement uses CONTINUE Keyword with while loop statement which checks whether the variable value is either 2 or 4. If the variable value is 2 or 4, then control is passed to the IF…ELSE statement blocks and execute the code which increment the value of the variable, execute the CONTINUE statement and starts a new iteration.
DECLARE @Seq INT SET @Seq=1 WHILE ( @Seq <= 5) BEGIN IF @Seq =2 or @Seq =4 BEGIN SET @Seq = @Seq + 1 CONTINUE END PRINT 'Row Number is = ' + CONVERT(VARCHAR,@Seq) SET @Seq = @Seq + 1 END
Also Read..
