Home » SQL Server WHILE

SQL Server WHILE

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 Satatement 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


 

 

SQL IF..ELSE

SQL GOTO Statement

SQL WaitFor




Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.