The SQL Break statement is useful to exit from SQL While loop. While executing the loop, if it finds the SQL Server break statement inside the While loop, it will stop running the query and immediately exit from the loop.
For example, we have 100 lines inside the While loop, and we want to exit from the loop when a certain condition is True; otherwise, it has to execute all of them. In this situation, we can use SQL IF ELSE or SQL ELSE IF to check for the condition and place the SQL Break statement inside the If block. If the condition is True, it will run the break statement, and the break will exit the controller from the loop completely. Otherwise, it will execute all the lines.
SQL Break Syntax
The basic syntax of the Break Statement in SQL Server is as follows:
SQL Break Statement example
In this query, We are going to use the SQL break statement inside the While loop to exit from the loop iteration.
DECLARE @Val INT SET @Val = 10 WHILE @Val > 0 BEGIN IF (@Val = 5) BREAK PRINT 'Tutorial Gateway Views = ' + CONVERT(VARCHAR, @Val) SET @Val= @Val - 1 END PRINT 'This statement is Coming from Outside the While Loop' GO
Within this SQL BREAK Statement example, First, we created one variable called @Val and initialized it to 10 using the following declaration
DECLARE @Val INT SET @Val = 10
Within the While loop, we check for the condition whether @Val is greater than 0 or not. I suggest you to refer SQL While article to understand the iteration process.
WHILE @Val > 0
Inside the While loop, we placed SQL IF ELSE to test whether @Val is equal to 5 (IF (@Val = 5)). If the condition is false, it skips the Break statement and prints the following message as output (In Our case 10, 9, 8, 7, 6). Please try Else if also.
PRINT 'Tutorial Gateway Views = ' + CONVERT(VARCHAR, @Val)
If this condition is True, the SQL Break statement executed and the While loop iteration will stop at that number (i.e., 5)The following print is outside the While Loop, and it has nothing to do with the expression in the While. It means, irrespective of condition result, this message will execute.
The following SQL Server statement is outside the While Loop, and it has nothing to do with the expression in the While loop. It means, irrespective of condition result, this print message will execute.
PRINT 'This statement is Coming from Outside the While Loop'