How to continue to the next iteration of a WHILE loop in SQL Server Transact-SQL? How to use CONTINUE statements?

You can use the CONTINUE statement to continue to the next iteration of a WHILE loop by skipping the rest of statement block of the current iteration using this syntax:

WHILE condition 
   BEGIN
      statement_1
      statement_2
      ...
      CONTINUE;
      statement_n
      ...
   END

When a CONTINUE statement inside a WHILE loop is executed, the system will:

• End the current iteration immediately and continue to the next iteration starting to evaluate the condition.

Here is an example on how to use CONTINUE statements inside WHILE loops:

DECLARE @count INT = 0, @sum INT = 0;
WHILE @count <= 10 BEGIN
   SET @count = @count+1;
   IF @count%2=0 CONTINUE;
   SET @sum = @sum + @count;
END
PRINT 'Sum of 1 to 10: ' + CAST(@sum AS CHAR);

Here is another example on how to use a CONTINUE statement to skip the remaining statements and continue the next iteration:

-- Printing first 7 Sundays in 2000
DECLARE @date DATETIME;
DECLARE @count INT;
SET @date = '1999-12-31'; 
SET @count = 0;
WHILE DATEPART(YEAR, @date) < 2001 BEGIN
   SET @date = DATEADD(DAY, 1, @date);
   IF @count = 7 BREAK; 
   IF DATENAME(WEEKDAY, @date) <> 'Sunday' CONTINUE; 
   PRINT CONVERT(VARCHAR(40),@date,107);
   SET @count = @count + 1;
   END
GO
Jan 02, 2000
Jan 09, 2000
Jan 16, 2000
Jan 23, 2000
Jan 30, 2000
Feb 06, 2000
Feb 13, 2000

 

⇒Conditional Statements and Loops in SQL Server in SQL Server Transact-SQL

⇒⇒SQL Server Transact-SQL Tutorials