Home >> FAQs/Tutorials >> SQL Server FAQ

SQL Server FAQ - BREAK - Stopping WHILE Loops Early

By: FYIcenter.com

(Continued from previous topic...)

How To Stop a Loop Early with BREAK Statements?

If you want to stop a WHILE loop early, you can use the BREAK statement in the loop statement block.

The tutorial exercise below shows you 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!

(Continued on next topic...)

1. How To Use "IF ... ELSE IF ... ELSE ..." Statement Structures?
2. How To Use "BEGIN ... END" Statement Structures?
3. How To Use WHILE Loops?
4. How To Stop a Loop Early with BREAK Statements?
5. How To Skip Remaining Statements in a Loop Block Using CONTINUE Statements?