BREAK (Control Interrupt) command in SQL Server

The Break command used to exit the loop does not specify a stop condition or you want to stop the loop on condition that you specify and execute the statements following the END command of the loop.

Syntax

To use the command to interrupt BREAK control in SQL Server, we use the following syntax:

 BREAK; 

There are no parameters and arguments in the BREAK statement.

Note:

1. You use BREAK to end the WHILE loop early.
2. If in the code there is WHILE nested LOOP, BREAK will terminate the nearest WHILE loop.

For example

 DECLARE @Number INT = 1; 
 DECLARE @Total INT = 0; 

 @Number WHILE <= 10 
 BEGIN 
 IF @NUMBER = 5 
 BREAK; 
 ELSE 
 SET @Total = @Total + @Number; 
 SET @Number = @Number + 1; 
 END 

 PRINT @Total; 
 GO 

In this example using the BREAK statement, the WHILE loop will end when @Number reaches value 5.

Previous article: FOR loop in SQL Server

Next lesson: CONTINUE command in SQL Server