** November 5, 2003
T-SQL Programming Part 2 - Building a T-SQL Loop **
** By Gregory A. Larsen **
This is the second article in my T-SQL programming series. This article will discuss building a program loop using T-SQL. In addition to talking about building a loop, I will also discuss ways of controlling the loop processing, and different methods to break out of a loop.
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
1<boolean expression=""> <code block="">
2
3Where 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.
4
5In this example I will increment a counter from 1 to 10 and display the counter each time through the WHILE loop.
6
7
8 declare @counter int
9 set @counter = 0
10 while @counter < 10
11 begin
12 set @counter = @counter + 1
13 print 'The counter is ' + cast(@counter as char)
14 end
15
16
17Here 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:
18
19
20 The counter is 1
21 The counter is 2
22 The counter is 3
23 The counter is 4
24 The counter is 5
25 The counter is 6
26 The counter is 7
27 The counter is 8
28 The counter is 9
29 The counter is 10
30
31
32As 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.
33
34Not 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.
35
36
37 declare @Part_Id int
38 declare @Category_Id int
39 declare @Desc varchar(50)
40 create table PART (Part_Id int, Category_Id int, Description varchar(50))
41 set @Part_Id = 0
42 set @Category_Id = 0
43 while @Part_Id < 2
44 begin
45 set @Part_Id = @Part_Id + 1
46 while @Category_Id < 3
47 begin
48 set @Category_Id = @Category_Id + 1
49 set @Desc = 'Part_Id is ' + cast(@Part_Id as char(1)) +
50 ' Category_Id ' + cast(@Category_Id as char(1))
51 insert into PART values(@Part_Id,
52 @Category_Id,
53 @Desc )
54 end
55 set @Category_Id = 0
56 end
57 select * from PART
58 drop table PART
59
60
61Here is the output from the SELECT statement at the bottom of this nested WHILE loop example.
62
63
64 Part_Id Category_Id Description
65 ----------- ----------- -----------------------------------------
66 1 1 Part_Id is 1 Category_Id 1
67 1 2 Part_Id is 1 Category_Id 2
68 1 3 Part_Id is 1 Category_Id 3
69 2 1 Part_Id is 2 Category_Id 1
70 2 2 Part_Id is 2 Category_Id 2
71 2 3 Part_Id is 2 Category_Id 3
72
73
74As 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.
75
76
77### BREAK and CONTINUE Statements
78
79Now 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.
80
81The 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.
82
83For 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.
84
85
86 declare @Part_Id int
87 declare @Category_Id int
88 declare @Desc varchar(50)
89 create table PART (Part_Id int, Category_Id int, Description varchar(50))
90 set @Part_Id = 0
91 set @Category_Id = 0
92 while @Part_Id < 2
93 begin
94 set @Part_Id = @Part_Id + 1
95 while @Category_Id < 3
96 begin
97 set @Category_Id = @Category_Id + 1
98 If @Category_ID = 2 and @Part_ID = 1
99 Break
100 set @Desc = 'Part_Id is ' + cast(@Part_Id as char(1)) +
101 ' Category_Id ' + cast(@Category_Id as char(1))
102 insert into PART values(@Part_Id,
103 @Category_Id,
104 @Desc )
105 end
106 set @Category_Id = 0
107 end
108 select * from PART
109 drop table PART
110
111
112Here is the output for this code that contains a BREAK statement inside the inner WHILE loop.
113
114
115 Part_Id Category_Id Description
116 ----------- ----------- -----------------------------------------
117 1 1 Part_Id is 1 Category_Id 1
118 2 1 Part_Id is 2 Category_Id 1
119 2 2 Part_Id is 2 Category_Id 2
120 2 3 Part_Id is 2 Category_Id 3
121
122
123From 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.
124
125Now 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.
126
127
128 declare @Part_Id int
129 declare @Category_Id int
130 declare @Desc varchar(50)
131 create table PART (Part_Id int, Category_Id int, Description varchar(50))
132 set @Part_Id = 0
133 set @Category_Id = 0
134 while @Part_Id < 2
135 begin
136 set @Part_Id = @Part_Id + 1
137 while @Category_Id < 3
138 begin
139 set @Category_Id = @Category_Id + 1
140 If @Category_ID = 2 and @Part_ID = 1
141 Continue
142 set @Desc = 'Part_Id is ' + cast(@Part_Id as char(1)) +
143 ' Category_Id ' + cast(@Category_Id as char(1))
144 insert into PART values(@Part_Id,
145 @Category_Id,
146 @Desc )
147 end
148 set @Category_Id = 0
149 end
150 select * from PART
151 drop table PART
152
153
154When you use the CONTINUE statement you get the following output.
155
156
157 ----------- ----------- -----------------------------------------
158 1 1 Part_Id is 1 Category_Id 1
159 1 3 Part_Id is 1 Category_Id 3
160 2 1 Part_Id is 2 Category_Id 1
161 2 2 Part_Id is 2 Category_Id 2
162 2 3 Part_Id is 2 Category_Id 3
163
164
165As 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.
166
167### GOTO Statement
168
169The 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.
170
171
172 declare @Part_Id int
173 declare @Category_Id int
174 declare @Desc varchar(50)
175 create table PART (Part_Id int, Category_Id int, Description varchar(50))
176 set @Part_Id = 0
177 set @Category_Id = 0
178 while @Part_Id < 2
179 begin
180 set @Part_Id = @Part_Id + 1
181 while @Category_Id < 3
182 begin
183 set @Category_Id = @Category_Id + 1
184 If @Category_ID = 3 and @Part_ID = 1
185 GOTO BREAK_OUT
186 set @Desc = 'Part_Id is ' + cast(@Part_Id as char(1)) +
187 ' Category_Id ' + cast(@Category_Id as char(1))
188 insert into PART values(@Part_Id,
189 @Category_Id,
190 @Desc )
191 end
192 set @Category_Id = 0
193 end
194 BREAK_OUT:
195 select * from PART
196 drop table PART
197
198
199Here is the output from this GOTO code:
200
201
202 Part_Id Category_Id Description
203 ----------- ----------- -----------------------------------------
204 1 1 Part_Id is 1 Category_Id 1
205 1 2 Part_Id is 1 Category_Id 2
206
207
208Here 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.
209
210### Conclusion
211
212Hopefully 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.</boolean></code></boolean></code></boolean>