Commands immediately following the END command are then executed. Example 19.8
illustrates a simple use of the BREAK command. It checks for some arbitrary number (say
@COUNT = 8). When this condition is met, it breaks out of the WHILE loop.
Example 19.8
Notice the placement of the BREAK statement after the evaluation of the first condition in the IF.
INPUT:
1> declare @COUNT int
2> select @COUNT = 1
3> while (@COUNT < 10)
4> begin
5> select @COUNT = @COUNT + 1
6> if (@COUNT = 8)
7> begin
8> break
9> end
10> else
11> begin
12> print "LOOP AGAIN!"
13> end
14> end
15> print "LOOP FINISHED!"
ANALYSIS:
The BREAK command caused the loop to be exited when the @COUNT variable equaled 8.
The CONTINUE Command
The CONTINUE command is also a special command that can be executed from within a WHILE loop. The CONTINUE command forces the loop to immediately jump back to the beginning, rather than executing the remainder of the loop and then jumping back to
the beginning. Like the BREAK command, the CONTINUE command is often used with an IF
statement to check for some condition and then force an action, as shown in Example
19.9.
Example 19.9
Notice the placement of the CONTINUE statement after the evaluation of the first
condition in the IF.
INPUT:
1> declare @COUNT int
2> select @COUNT = 1
3> while (@COUNT < 10)
4> begin
5> select @COUNT = @COUNT + 1
6> if (@COUNT = 8)
7> begin
8> continue
9> end
10> else
11> begin
12> print "LOOP AGAIN!"
13> end
14> end
15> print "LOOP FINISHED!"
ANALYSIS:
Example 19.9 is identical to Example 19.8 except that the CONTINUE command replaces the BREAK command. Now instead of exiting the loop when @COUNT = 8, it simply jumps back to the top of the WHILE statement and continues.
Using the WHILE Loop to Scroll Through a Table
SQL Server and many other database systems have a special type of object--the cursor--
that enables you to scroll through a table's records one record at a time. (Refer to Day 13.) However, some database systems (including SQL Server pre-System 10) do not support
the use of scrollable cursors. Example 19.10 gives you an idea of how to use a WHILE loop to implement a rough cursor-type functionality when that functionality is not
automatically supplied.
Example 19.10
You can use the WHILE loop to scroll through tables one record at a time. Transact-SQL
stores the rowcount variable that can be set to tell SQL Server to return only one row at a time during a query. If you are using another database product, determine whether
your product has a similar setting. By setting rowcount to 1 (its default is 0, which means unlimited), SQL Server returns only one record at a time from a SELECT query.
You can use this one record to perform whatever operations you need to perform. By
selecting the contents of a table into a temporary table that is deleted at the end of
the operation, you can select out one row at a time, deleting that row when you are
finished. When all the rows have been selected out of the table, you have gone through
every row in the table! (As we said, this is a very rough cursor functionality!) Let's run the example now.
INPUT:
1> set rowcount 1
2> declare @PLAYER char(30)
3> create table temp_BATTERS (
4> NAME char(30),
5> TEAM int,
6> AVERAGE float,
7> HOMERUNS int,
8> RBIS int)
9> insert temp_BATTERS
10> select * from BATTERS
11> while exists (select * from temp_BATTERS)
12> begin
13> select @PLAYER = NAME from temp_BATTERS
14> print @PLAYER
15> delete from temp_BATTERS where NAME = @PLAYER
16> end
17> print "LOOP IS DONE!"
ANALYSIS:
Note that by setting the rowcount variable, you are simply modifying the number of rows returned from a SELECT. If the WHERE clause of the DELETE command returned five rows, five rows would be deleted! Also note that the rowcount variable can be reset
repeatedly. Therefore, from within the loop, you can query the database for some
additional information by simply resetting rowcount to 1 before continuing with the loop.
Transact-SQL Wildcard Operators