Building a T-SQL Loop

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 would discuss building a program loop using T-SQL. In addition to talking about building a loop, I'll also discuss ways of controlling the loop processing, and different m Ethods to break out of a loop.

A programming loop is a chunk of code this is executed over and over again. In the loop some logic are executed repeatedly in a iterative fashion until some condition is met which allows the code to Break out of the loop. One example of where you might the use a loop would is to process through a set of records one record in a time. Another example might is where you need to generate some test data and a loop would allow the to insert a record into your Test data table with slightly different column values, which is executed of the loop is. In this article I'll discuss the while, break, CONTINUE, and GOTO statements.
While Statement
In T-SQL the while statement are the most commonly used way to execute a loop. Here's the basic syntax for a while loop:

While <boolean expression> <code block>

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

In this example I'll increment a counter from 1 to all and display the counter each time through the while loop.

DECLARE @counter intset @counter = 0while @counter < 10begin set @counter = @counter + 1 print ' The counter is ' + cast (@counter as Char) end

Here's the code executes the while statement as long as the @counter an integer variable is less than, this is the Boolean E Xpression of the While loop. The @counter variable starts out in 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 of the counter is 2 the counter are 3 the counter is 4 the counter are 5 the counter is 6 the counter is 7 the Counter is 8 The counter are 9 the counter is 10

As you can, once the @counter variable reaches the Boolean expression this is controlling the while loop is no long Er true, so the code within the "while" loop is no longer executed.

Not only can you have a single while loop, but 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 the while loops to generate test data. My next example'll use the "while" loop to generate test records for a part table. A given part the uniquely identified by a part_id, and a category_id. For each part_id there are three different category_id ' s. This is my example this generates 6 unique records for the My part table using a nested while loop.

DECLARE @Part_Id intdeclare @Category_Id intdeclare @Desc varchar CREATE TABLE part (part_id int, category_id int, Desc Ription varchar Set @Part_Id = 0set @Category_Id = 0 while @Part_Id < 2begin set @Part_Id = @Part_Id + 1 while @Cat EGORY_ID < 3 begin Set @Category_Id = @Category_Id + 1 Set @Desc = ' part_id is ' + cast (@Part_Id as char (1)) + ' Catego ry_id ' + cast (@Category_Id as char (1)) insert into part values (@Part_Id, @Category_Id, @Desc) End Set @Category_Id = 0 E Ndselect * from Partdrop table part

Here's the output from the SELECT statement in the bottom of this nested while loop example.

part_id category_id Description---------------------------------------------------------------1 1 part_id is 1 catego RY_ID 2 part_id is 1 category_id 3 part_id are 1 category_id 1 part_id is 2 category_id a 2 part_id is 2 Category _ID 3 part_id is 2 category_id 3

As you can, by using a nested while loop each combination the part_id and category_id is unique. The code within the "the" controlled the incrementing of the The part_id, where as the second while loop set the Ca tegory_id to a different value of each time through the loop. The code within the executed only twice, but the code inside the second while loop is executed 6 Tim Es. Thus giving me 6 sample part records.

Break and CONTINUE statements
Now sometimes your want to builds a loop that'll process through logically to the "end most of the" time, but does all the TI Me. In the "other words", you could want to break out of the loop if some particular condition arises. Also In addition to breaking out of the loop, if you are not want to process all the "code in" loop before going back to th e Top of the loop and starting through the next iteration of the loop. For this 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" 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 the "the" and "the" the "the" the "the" the "current" while loop. Let's go though a couple the break and CONTINUE examples.

For the "break statement I ' m" Going to modify me last example this generated part table records. This time I ' m going to-break out of the inner while loop when category_id are 2 and part_id is 1. This is the Me code for the break statement.

DECLARE @Part_Id intdeclare @Category_Id intdeclare @Desc varchar CREATE TABLE part (part_id int, category_id int, Desc Ription varchar Set @Part_Id = 0set @Category_Id = 0 while @Part_Id < 2begin set @Part_Id = @Part_Id + 1 while @Cat EGORY_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)) inserts into the part values (@Part_Id, @Cate gory_id, @Desc) End Set @Category_Id = 0 Endselect * to Partdrop table part

Here is the output for this code, contains a break statement inside the inner while loop.

part_id category_id Description---------------------------------------------------------------1 1 part_id is 1 catego ry_id 1 part_id is 2 category_id 2 part_id are 2 category_id 3 part_id is 2 category_id 3

From here output you can do so no records were inserted for part_id = 1 and category_id =2 or 3, where as there are rec Ords for part_id = 2 and all values for the category_id column. This is because the "IF statement in" Inner Loop forced the break statement to exit the inner loop. Since there were records generate for part_id = 2, shows this break statement only exited the inner loop and not the O Uter 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 Continu E statement. This is the code for demonstrating the CONTINUE statement.

DECLARE @Part_Id intdeclare @Category_Id intdeclare @Desc varchar CREATE TABLE part (part_id int, category_id int, Desc Ription varchar Set @Part_Id = 0set @Category_Id = 0 while @Part_Id < 2begin set @Part_Id = @Part_Id + 1 while @Cat EGORY_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)) inserts into the part values (@Part_Id, @c ategory_id, @Desc) End Set @Category_Id = 0 Endselect * to Partdrop table part

When you use the CONTINUE statement the following output.

---------------------------------------------------------------1 1 part_id is 1 category_id one 3 part_id is 1 category_ Id 1 part_id is 2 category_id 2 part_id are 2 category_id 3 part_id is 2 category_id 3

As you can if I use the "CONTINUE statement only" with category_id = 2 and part_id = 1 is missing. This are because the CONTINUE statement does not break out of the inner while loop but only goes LE loop without inserting the record. This happens only if category_id is 2 and part_id are equal to 1. When part_id = 1 and category_id = 3 The INSERT statement is still executed.
GOTO Statement
The break statement'll only exit to the currently processing while loop, it'll not be break out of the all while loops. However, occasionally this is the kind of functionality your T-SQL script needs. To have your code break out of the all while loops, no matter how many nested while statements your have, you'll 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 a exce PT able Practice. Using My same example I'll use the "GOTO" break out of both while loops, when the part_id = 1 and the category_id=3.

DECLARE @Part_Id intdeclare @Category_Id intdeclare @Desc varchar CREATE TABLE part (part_id int, category_id int, Desc Ription varchar Set @Part_Id = 0set @Category_Id = 0 while @Part_Id < 2begin set @Part_Id = @Part_Id + 1 while @Cat EGORY_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)) inserts into the part values (@Part_ Id, @Category_Id, @Desc) End Set @Category_Id = 0 Endbreak_out:select * Partdrop table part

Here are the output from this GOTO code:

part_id category_id Description---------------------------------------------------------------1 1 part_id is 1 catego RY_ID 2 part_id is 1 category_id 2

Here's the GOTO logic stopped the insertion of records into the "part table" When @Category_ID = 3 and @Part_Id = 1. This is doing by executing the "GOTO breakout" statement. Note So when this GOTO statement is executed it branched to the label ' Break Out: ' which can be found following ' end Statement for the "the", outer most while statement.
Conclusion
Hopefully now and 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 "the" while loop where the and/or break out of the all while loops using the GOTO statement. The techniques I ' ve described should give you on the basis for building all your while statements from a single while loops to A complex set of nested while loops. My next article in this series would discuss how to process through a set of records.