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, 485👍, 0💬
How To Import One Table Back from a Dump File in Oracle? If you only want to import one table back t...
Where to find answers to frequently asked questions on Using User Defined Functions in SQL Server Tr...
What are binary literals supported in SQL Server Transact-SQL? Binary literals in Transact-SQL are s...
How To Generate Random Numbers with the RAND() Function in SQL Server Transact-SQL? Random numbers a...
How To Provide Values to Stored Procedure Parameters in SQL Server Transact-SQL? If a stored procedu...