You are currently viewing A T-SQL WHILE loop demonstration with BREAK and CONTINUE
Microsoft SQL Server

A T-SQL WHILE loop demonstration with BREAK and CONTINUE

Just a short blog post to demonstrate the use of a WHILE loop in T-SQL, together with the BREAK and CONTINUE statements.

A WHILE loop executes a statement block repeatedly until a specified condition is no longer true. Inside the loop, the execution can be controlled with the BREAK and CONTINUES statements. BREAK causes an exit from the loop and CONTINUE causes the loop to restart and move to the next iteration.

In the T-SQL code below, a loop increments an value (@COUNTER) from 0 to 10 and prints it. At 7, the CONTINUE statement is demonstrated. 7 is never printed, since CONTINUE restarts the loop. At 9, the BREAK statement is demonstrated. 9 and 10 is never printed, since BREAK exits the loop.

DECLARE @COUNTER INT = 0
WHILE @COUNTER < 10 BEGIN
	SET @COUNTER = @COUNTER + 1

	IF @COUNTER = 7
		CONTINUE

	IF @COUNTER = 9
		BREAK

	PRINT @COUNTER
END
WHILE Loop Demonstration With BREAK And CONTINUE
WHILE Loop Demonstration With BREAK And CONTINUE

A common scenario where BREAK and CONTINUE can be used is together with error handling in a loop, when you for instance just want to move to the next iteration in case of an error. Or perhaps exit the loop completely when an error occurs, not iterating through the rest.

Tomas Lind

Tomas Lind - Consulting services as SQL Server DBA and Database Developer at High Coast Database Solutions AB.

This Post Has 4 Comments

  1. Kleidi

    Thanks

  2. Rudy Hermanto

    Simple but clear

  3. Joe Dimagio

    Good example. Thanks!

  4. CFOX

    it might be best to have the WHILE condition check for < 15, to better illustrate a break.
    If you leave it at < 10, the break does exactly the same thing as CONTINUE, since the @COUNTER on the next iteration would now be = 10 and the loop would stop anyway.

Leave a Reply