Development SQL Server Stored Procedures

Break

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:

1234567WHILE 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:

123456789WHILE 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:

123456789DECLARE @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.

Leave a Reply

Your email address will not be published. Required fields are marked *