Tutorial Gateway

SQL CONTINUE Statement

by

The SQL Continue statement is very useful to control the flow of a SQL While loop. Generally, we use this statement inside the While loop, and if the execution finds the continue statement inside the While loop, it will stop executing the current loop iteration and starts the new iteration from the beginning.

For example, If we have 15 statements inside the loop and we want to skip executing few statements (statement 2 — statement 6, and statement 11 — statement 14) when certain condition is True otherwise, it has to execute all the 15 statements inside the loop. In this situation we can place the SQL IF ELSE or SQL ELSE IF to check for the condition, and within the if block place the continue statement. If the condition is True, it will stop executing statement 2 to 6, and 11 to 14 otherwise, it will execute statements from 1 to 15.

SQL CONTINUE Syntax

The basic syntax of the Continue Statement in SQL Server is as follows:

CONTINUE;

SQL CONTINUE Statement example

In this SQL query, We are going to use the Continue statement inside the While loop to control the loop iteration.

SQL CODE

-- SQL CONTINUE Statement Example
DECLARE @Val INT
SET @Val = 1

WHILE @Val <= 10
	BEGIN
		IF (@Val = 3 OR @Val = 7)
		BEGIN
			PRINT 'Skipped By Continue Statement = ' + CONVERT(VARCHAR, @Val)
			SET @Val= @Val + 1
			CONTINUE
		END
		PRINT 'Tutorial Gateway Views = ' + CONVERT(VARCHAR, @Val)
		SET @Val= @Val + 1
	END
PRINT 'This statement is Coming from Outside the While Loop'
GO

OUTPUT

SQL CONTINUE Statement 1

ANALYSIS

First, we created one variable called @Val and initialized it to 10 using the following statement

DECLARE @Val INT
SET @Val = 1

Within the While loop, we check for the condition whether @Val is less than 10 or not. I suggest you to refer SQL While Loop article to understand the iteration process.

WHILE @Val <= 10

Inside the While loop we placed SQL IF ELSE to test whether @Val is equal to 3 or 7 (IF (@Val = 3 OR @Val = 7)). If the condition is false, it will skip the Continue statement and prints the following statement as output (In Our case 1, 2, 4, 5, 6, 8, 9, 10).

PRINT 'Tutorial Gateway Views = ' + CONVERT(VARCHAR, @Val)

If this condition is True, Continue statement will be executed and the While loop iteration will stop at that number (i.e., 3 and 7) without printing the other statements. For better understanding, we placed the below statement inside the If block. So, whenever the iteration break, that value will be printed from this statement.

PRINT 'Skipped By Continue Statement = ' + CONVERT(VARCHAR, @Val)

Following statement is outside the While Loop, and it has nothing to do with the expression in While loop. It means, irrespective of condition result, this statement will execute.

PRINT 'This statement is Coming from Outside the While Loop'

Thank you for visiting our Blog

Placed Under: SQL