The SQL Break statement is very useful to exit from SQL While loop. While executing the loop, if it finds the break statement inside the While loop, it will stop executing the statements and immediately exit from the loop.
For example, we have 100 statements inside the While loop, and we want to exit from the loop when 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 Break statement inside the If block. If the condition is True, compiler will execute the break statement, and the break statement will exit the controller from the loop completely otherwise, it will execute all the statements.
SQL Break Syntax
The basic syntax of the Break Statement in SQL Server 2014 is as follows:
SQL Break Statement example
In this SQL query, We are going to use the 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
First, we created one variables called @Val and initialized it to 10 using the following statement
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 Loop 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 then it will skip the Break statement and prints the following statement as output (In Our case 10, 9, 8, 7, 6).
PRINT 'Tutorial Gateway Views = ' + CONVERT(VARCHAR, @Val)
If this condition is True then Break statement will be executed and the While loop iteration will stop at that number (i.e., 5)
Following statement is outside the While Loop, and it has nothing to do with the expression in While loop. It means, irrespective of condition result, this statement will execute.
PRINT 'This statement is Coming from Outside the While Loop'
Thank you for visiting our Blog