A programming loop is a chunk of code that is executed over and over again. In the loop some logic is executed repeatedly in an iterative fashion until some condition is met that allows the code to break out of the loop. One example of where you might use a loop would be to process through a set of records one record at a time. Another example might be where you need to generate some test data and a loop would allow you to insert a record into your test data table with slightly different column values, each time the loop is executed. In this article I will discuss the WHILE, BREAK, CONTINUE, and GOTO statements.

WHILE Statement

In T-SQL the WHILE statement is the most commonly used way to execute a loop. Here is the basic syntax for a WHILE loop:

	WHILE <Boolean expression> <code block>

Where a <Boolean expression> is any expression that equates to a true or false answer, and the <code block> is the desire code to be executed while the <Boolean expression> is true. Let''''s go through a real simple example.

In this example I will increment a counter from 1 to 10 and display the counter each time through the WHILE loop.

	declare @counter int
	set @counter = 0
	while @counter < 10
	begin
	  set @counter = @counter + 1
	  print ''''The counter is '''' + cast(@counter as char)
	end

Here the code executes the WHILE statement as long as the @counter integer variable is less than 10, this is the Boolean expression of the WHILE loop. The @counter variable starts out at zero, and each time through the WHILE loop it is incremented by 1. The PRINT statement displays the value in the @counter variable each time through the WHILE loop. The output from this sample looks like this:

	The counter is 1                             
	The counter is 2                             
	The counter is 3                             
	The counter is 4                             
	The counter is 5                             
	The counter is 6                             
	The counter is 7                             
	The counter is 8                             
	The counter is 9                             
	The counter is 10        

As you can see, once the @counter variable reaches 10 the Boolean expression that is controlling the WHILE loop is no longer true, so the code within the while loop is no longer executed.

Not only can you have a single while loop, but you can have WHILE loops inside WHILE loops. Or commonly know as nesting of WHILE loops. There are lots of different uses where nesting is valuable. I commonly use nesting of WHILE loops to generate test data. My next example will use the WHILE loop to generate test records for a PART table. A given PART record is uniquely identified by a Part_Id, and a Category_Id. For each Part_Id there are three different Category_Id''''s. Here is my example that generates 6 unique records for my PART table using a nested WHILE loop.

	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
	    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 from the SELECT statement at the bottom of this nested WHILE loop example.

	Part_Id     Category_Id Description                                        
	----------- ----------- ----------------------------------------- 
	1           1           Part_Id is 1 Category_Id 1
	1           2           Part_Id is 1 Category_Id 2
	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, by using a nested WHILE loop each combination of Part_Id and Category_Id is unique. The code within the first WHILE loop controlled the incrementing of the Part_Id, where as the second WHILE loop set the Category_Id to a different value each time through the loop. The code within the first while loop was executed only twice, but the code inside the second WHILE loop was executed 6 times. Thus giving me 6 sample PART records.

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 Categor

[1] [2]  下一页