SQL BREAK Statement

The SQL Break statement is useful to exit from the 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 IF ELSE or 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:

BREAK;

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
SQL BREAK Statement 1

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 refer to the While article to understand the iteration process.

WHILE @Val > 0

Inside the While loop, we placed 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 will be 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 the 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 the condition result, this print message will execute.

PRINT 'This statement is Coming from Outside the While Loop'
Categories SQL