How to break a WHILE look statement in SQL Server Transact-SQL? How to use BREAK statements?

You can use the BREAK statement to break a WHILE loop inside the statement block in Transact-SQL using this syntax:

WHILE condition 
   BEGIN
      statement_1
      statement_2
      ...
      BREAK;
      statement_n
      ...
   END

When a BREAK statement inside a WHILE loop is executed, the system will:

• End the WHILE loop immediately.

Here is an example on how to use BREAK statements inside WHILE loops:

DECLARE @count INT = 1, @sum INT = 0;
WHILE 1=1 BEGIN
   SET @sum = @sum + @count;
   SET @count = @count+1;
   IF @count > 10 BREAK;
END
PRINT 'Sum of 1 to 10: ' + CAST(@sum AS CHAR);

Here is another example on how to use a BREAK statement to stop a WHILE loop early:

-- Counting number of days in 2000
DECLARE @date DATETIME;
DECLARE @count INT;
SET @date = '2000-01-01'; 
SET @count = 0;
WHILE 1=1 BEGIN
   IF DATEPART(YEAR, @date) > 2000 BREAK; 
   SET @count = @count + 1;
   SET @date = DATEADD(DAY, 1, @date);
   END
SELECT @count;
366
-- 2000 is a leap year!

⇒ CONTINUE to Next Loop Iteration in SQL Server Transact-SQL

⇐ WHILE ... Loops in SQL Server Transact-SQL

⇑ Conditional Statements and Loops in SQL Server in SQL Server Transact-SQL

⇑⇑ SQL Server Transact-SQL Tutorials