CONTINUE to Next Loop Iteration in SQL Server Transact-SQL
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:
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
2017-01-11, 2758👍, 0💬
Where to find answers to frequently asked questions on CREATE, ALTER and DROP Statements in MySQL? H...
How To Convert Binary Strings into Hexadecimal Character Strings in SQL Server? When a query returns...
How To Use GO Command in "sqlcmd" in SQL Server? "sqlcmd" is a command line client application to ru...
How To Provide Values to Stored Procedure Parameters in SQL Server Transact-SQL? If a stored procedu...
How To Create a Stored Function in Oracle? A stored function is a function with a specified name and...