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.

上一页  [1] [2]