Sql server® 2012 t-sql fundamentals

 

CHAPTER 10

 

Programmable Objects

 

 347

1 

2 

3 

4 

5 

6 

7 

8 

9 

10

If at some point in the loop’s body you want to break out of the current loop and proceed to exe-

cute the statement that appears after the loop’s body, use the BREAK command. For example, the 

following code breaks from the loop if the value of @i is equal to 6.

DECLARE @i AS INT = 1; 

WHILE @i <= 10 

BEGIN 

  IF @i = 6 BREAK; 

  PRINT @i; 

  SET @i = @i + 1; 

END;

This code produces the following output showing that the loop iterated five times and terminated 

at the beginning of the sixth iteration.

1 

2 

3 

4 

5

Of course, this code is not very sensible; if you want the loop to iterate only five times, you should 

simply specify the predicate @i <= 5. Here I just wanted to demonstrate the use of the BREAK com-

mand with a simple example.

If at some point in the loop’s body you want to skip the rest of the activity in the current iteration 

and evaluate the loop’s predicate again, use the CONTINUE command. For example, the following 

code demonstrates how to skip the activity of the sixth iteration of the loop from the point where the 

IF statement appears and until the end of the loop’s body.

DECLARE @i AS INT = 0; 

WHILE @i < 10 

BEGIN 

  SET @i = @i + 1; 

  IF @i = 6 CONTINUE; 

  PRINT @i; 

END;

www.it-ebooks.info

---

348  

Microsoft SQL Server 2012 T-SQL Fundamentals

The output of this code shows that the value of @i was printed in all iterations but the sixth.

1 

2 

3 

4 

5 

7 

8 

9 

10