SQL Server BREAK statement overview
In the previous tutorial, you have learned how to use the WHILE statement to create a loop. To exit the current iteration of a loop, you use the BREAK statement.
The following illustrates the typical syntax of the BREAK statement:
| 1234567 | WHILE Boolean_expressionBEGIN — statements IF condition BREAK; — other statements END |
In this syntax, the BREAK statement exit the WHILE loop immediately once the condition specified in the IF statement is met. All the statements between the BREAK and END keywords are skipped.
Suppose we have a WHILE loop nested inside another WHILE loop:
| 123456789 | WHILE Boolean_expression1BEGIN — statement WHILE Boolean_expression2 BEGIN IF condition BREAK; ENDEND |
In this case, the BREAK statement only exits the innermost loop in the WHILE statement.
Note that the BREAK statement can be used only inside the WHILE loop. The IF statement is often used with the BREAK statement but it is not required.
SQL Server BREAK statement example
The following example illustrates how to use the BREAK statement:
| 123456789 | DECLARE @counter INT = 0; WHILE @counter <= 5BEGIN SET @counter = @counter + 1; IF @counter = 4 BREAK; PRINT @counter;END |
In this example:
First, we declared a variable named @counter and set its value to zero.
Then, we used the WHILE statement to increases the @counter by one in each iteration and print out the @counter‘s value as long as the value of the @counter is less than or equal to five.
Inside the loop, we also checked if the value of @counter equals four, then we exited the loop. In the fourth iteration, the value of the counter reached 4, then the loop is terminated. Also, the PRINT statement after the BREAK statement was skipped.