SQL SERVER – Simple Example of WHILE Loop With CONTINUE and BREAK Keywords

The WHILE loop in SQL Server is straightforward to understand and widely used for iterative operations. While most users grasp its functionality quickly, a few might find it slightly confusing initially. This blog explains the simple usage of the WHILE loop along with the keywords CONTINUE and BREAK, which control the flow within the loop. Let us learn about break keywords.

The BREAK keyword terminates the WHILE loop entirely, transferring control to the first statement after the loop. In contrast, the CONTINUE keyword skips the remaining statements in the current iteration and jumps back to the beginning of the loop for the next iteration.

1) Example of WHILE Loop

DECLARE @intFlag INT
SET @intFlag = 1
WHILE (@intFlag <=5)
BEGIN
PRINT @intFlag
SET @intFlag = @intFlag + 1
END
GO

ResultSet:
1
2
3
4
5

2) Example of WHILE Loop with BREAK keyword

DECLARE @intFlag INT
SET @intFlag = 1
WHILE (@intFlag <=5)
BEGIN
PRINT @intFlag
SET @intFlag = @intFlag + 1
IF @intFlag = 4
BREAK;
END
GO

Result

Set:
1
2
3

3) Example of WHILE Loop with CONTINUE and BREAK keywords

DECLARE @intFlag INT
SET @intFlag = 1
WHILE (@intFlag <=5)
BEGIN
PRINT @intFlag
SET @intFlag = @intFlag + 1
CONTINUE;
IF @intFlag = 4 -- This will never executed
BREAK;
END
GO

Result Set:

1
2
3
4
5

I hope you find this simple blog post helpful. Let me know what you think of this blog post. Please leave your views in the comment sections. These examples illustrate how WHILE loops and control keywords function in SQL Server. Feel free to share your thoughts in the comments below.

You can follow me on YouTube.

Reference: Pinal Dave (https://blog.sqlauthority.com)