CONTINUE to Next Loop Iteration in SQL Server Transact-SQL

Q

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

✍: FYIcenter.com

A

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

2017-01-11, 392👍, 0💬