BREAK and CONTINUE Statements
Now sometimes you want to
build a loop that will process through logically to the end most of the time,
but not all the time. In other words, you may want to break out of the loop if
some particular condition arises. Also in addition to breaking out of the
loop, you may not want to process all the code in the loop before going back to
the top of the loop and starting through the next iteration of the loop. For
these kinds of programming requirements SQL Server provides the BREAK and
CONTINUE statements.
The BREAK statement exits
out of the inner most WHILE loop, and proceeds to the statement following the
END statement that is associated with the loop in which the BREAK statement is
executed. The CONTINUE statement skips executing the rest of the statements between
the CONTINUE statement and the END statement of the current loop and starts
executing at the first line following the BEGIN statement of the current WHILE loop.
Let's go though a couple of BREAK and CONTINUE examples.
For the BREAK statement I'm
going to modify my last example that generated PART table records. This time
I'm going to BREAK out of the inner WHILE loop when Category_ID is 2 and
PART_ID is 1. Here is my code for the BREAK statement.
declare @Part_Id int
declare @Category_Id int
declare @Desc varchar(50)
create table PART (Part_Id int, Category_Id int, Description varchar(50))
set @Part_Id = 0
set @Category_Id = 0
while @Part_Id < 2
begin
set @Part_Id = @Part_Id + 1
while @Category_Id < 3
begin
set @Category_Id = @Category_Id + 1
If @Category_ID = 2 and @Part_ID = 1
Break
set @Desc = 'Part_Id is ' + cast(@Part_Id as char(1)) +
' Category_Id ' + cast(@Category_Id as char(1))
insert into PART values(@Part_Id,
@Category_Id,
@Desc )
end
set @Category_Id = 0
end
select * from PART
drop table PART
Here is the output for this
code that contains a BREAK statement inside the inner WHILE loop.
Part_Id Category_Id Description
----------- ----------- -----------------------------------------
1 1 Part_Id is 1 Category_Id 1
2 1 Part_Id is 2 Category_Id 1
2 2 Part_Id is 2 Category_Id 2
2 3 Part_Id is 2 Category_Id 3
From this output you can see
that no records were inserted for Part_Id = 1 and Category_Id =2 or 3, where as
there are records for Part_Id = 2 with all values for the Category_Id column. This
is because the IF statement in the inner loop forced the BREAK statement to
exit the inner loop. Since there were records generate for Part_Id = 2, shows
that the BREAK statement only exited the inner loop and not the outer loop.
Now just to stay with the
same example I've been using, let's replace the BREAK statement in the code
above with a CONTINUE statement. Here is the code for demonstrating the
CONTINUE statement.
declare @Part_Id int
declare @Category_Id int
declare @Desc varchar(50)
create table PART (Part_Id int, Category_Id int, Description varchar(50))
set @Part_Id = 0
set @Category_Id = 0
while @Part_Id < 2
begin
set @Part_Id = @Part_Id + 1
while @Category_Id < 3
begin
set @Category_Id = @Category_Id + 1
If @Category_ID = 2 and @Part_ID = 1
Continue
set @Desc = 'Part_Id is ' + cast(@Part_Id as char(1)) +
' Category_Id ' + cast(@Category_Id as char(1))
insert into PART values(@Part_Id,
@Category_Id,
@Desc )
end
set @Category_Id = 0
end
select * from PART
drop table PART
When you use the CONTINUE
statement you get the following output.
----------- ----------- -----------------------------------------
1 1 Part_Id is 1 Category_Id 1
1 3 Part_Id is 1 Category_Id 3
2 1 Part_Id is 2 Category_Id 1
2 2 Part_Id is 2 Category_Id 2
2 3 Part_Id is 2 Category_Id 3
As you can see, when I use
the CONTINUE statement only the record with Category_Id = 2 and Part_Id = 1 is
missing. This is because the CONTINUE statement does not break out of the
inner WHILE loop but only goes back to the top of the WHILE loop without
inserting the record. This happens only when Category_Id is 2 and Part_Id is equal
to 1. When Part_Id = 1 and Category_Id = 3 the insert statement is still
executed.
GOTO Statement
The BREAK statement will
only exit you from the currently processing WHILE loop, it will not break out of
all WHILE loops. However, occasionally this is the kind of functionality your
T-SQL script needs. To have your code break out of all WHILE loops, no matter
how many nested WHILE statements you have, you will need to use the GOTO
statement. Now I know most programmers cringe at the thought of using the GOTO
statement, but in this case I feel the GOTO is an except able practice. Using
my same example I will use the GOTO to break out of both WHILE loops, when the
PART_Id = 1 and the Category_ID=3.
declare @Part_Id int
declare @Category_Id int
declare @Desc varchar(50)
create table PART (Part_Id int, Category_Id int, Description varchar(50))
set @Part_Id = 0
set @Category_Id = 0
while @Part_Id < 2
begin
set @Part_Id = @Part_Id + 1
while @Category_Id < 3
begin
set @Category_Id = @Category_Id + 1
If @Category_ID = 3 and @Part_ID = 1
GOTO BREAK_OUT
set @Desc = 'Part_Id is ' + cast(@Part_Id as char(1)) +
' Category_Id ' + cast(@Category_Id as char(1))
insert into PART values(@Part_Id,
@Category_Id,
@Desc )
end
set @Category_Id = 0
end
BREAK_OUT:
select * from PART
drop table PART
Here is the output from this
GOTO code:
Part_Id Category_Id Description
----------- ----------- -----------------------------------------
1 1 Part_Id is 1 Category_Id 1
1 2 Part_Id is 1 Category_Id 2
Here the GOTO logic stopped
the insertion of records into the PART table when @Category_ID = 3 and @Part_Id
= 1. This is done by executing the "GOTO BREAKOUT" statement. Note that when
this GOTO statement was executed it branched to the label "BREAK OUT:" which
can be found following the END statement for the first, outer most WHILE
statement.
Conclusion
Hopefully now you have a
better idea of how to code a T-SQL WHILE loop. I've explained how to control
the WHILE loop, break out of a loop by using the BREAK statement, use the
CONTINUE statement to skip some of the code in the while loop, and/or break out
of all WHILE loops using the GOTO statement. The techniques I've described
should give you the basis for building all your WHILE statements from a single
WHILE loop to a complex set of nested WHILE loops. My next article in this
series will discuss how to process through a set of records.
»
See All Articles by Columnist Gregory A. Larsen
» 
Comment and Contribute