WHILE

The WHILE command repeats a statement until the WHILE test is false. This is great for repeating a command many times, or processing an unknown number of rows in a table. This example creates a table and inserts 1000 rows into it. You can use this to test insert performance of your server on a very small table. (These results are more of the “neato” than “useful” statistical variety. Every table will give different results based on a number of performance factors.)

/* Create a table. */
create  table t1(
        i int identity,
        d datetime not null default CURRENT_TIMESTAMP)
go

/* Declare a local variable for loop control. */
declare @ctr int

/* Initialize the counter to zero. */
select @ctr = 0

/* Perform the loop */
while @ctr < 1000 begin
      insert t1(d) values(DEFAULT)
      select @ctr = @ctr + 1
end

First, I create a table to hold some rows. The table has two columns. The first is an identity column, which will associate a number with the row. This will help determine the number of rows per second later on. I defined a default of the current date and time on the second column.

Next, declare an integer variable for looping. Initialize the counter to zero.

Last, perform the loop. The WHILE keyword is followed by the WHILE test. This is a boolean test, just as with IF. The code contained in the BEGIN... END code block will be executed repeatedly until the WHILE condition tests false. It’s very easy to accidentally create infinite loops. If you do this, the code will run until you close your connection to the server or cancel the query.

Now, the cool part. Select the data out of the table using this query:

select i, convert(varchar(20), d, 14) from t1

i
----------- --------------------
1           13:13:49:973
2           13:13:49:983
3           13:13:49:993
4           13:13:49:993
5           13:13:50:003
[...]
153         13:13:50:986
154         13:13:50:996
155         13:13:51:006
156         13:13:51:006
[...]
333         13:13:51:996
334         13:13:51:996
335         13:13:52:006
336         13:13:52:016
[...]

The query selects the identity column (for generated row numbers) and a formatted version of the date. Format #14 gives milliseconds with 1/300th-second accuracy. Go down the list and see how many rows were inserted in a second. For me, it’s about 160/s.

CONTINUE

There are two extra keywords that may be performed inside a WHILE loop. These keywords generate an error if they are issued outside a WHILE loop. The first of these is the CONTINUE keyword.

CONTINUE immediately jumps to the top of the WHILE loop and re-evaluates the condition. If the condition tests true, processing continues with the first statement in the WHILE code block. If the condition tests false, the loop is exited.

BREAK

The BREAK keyword is the second command that is usable inside a WHILE loop. BREAK immediately exits the WHILE loop.

RETURN

RETURN immediately exits the batch. You can use RETURN as the last statement in error-processing code to abort the batch.

When used with stored procedures, RETURN accepts, optionally, an integer argument. Inside stored procedures, values are commonly returned to indicate the success or failure of the procedure. There are more details on this in the stored procedure lessons, near the end of the book. When you use RETURN in a normal batch, you cannot return a value.

Here is the same example presented earlier today that exits if an error is encountered:

begin  transaction
update inventory
et     qty = qty + 5
where  item_id = 31415

select @err = @@error
if @err <> 0 begin
       rollback transaction
       insert upd_errors (err_time, errnum)
     values (CURRENT_TIMESTAMP, @err)
   return
end

The RETURN statement in this example returns from the batch without executing any statements following it.

The RAND Function

To generate a random number, use the RAND() function. This returns a random number between 0 and 1. To get a random integer, multiply the result of RAND by an integer and convert it. For example, to generate a number from zero to 99, use this query:

select convert(int, rand() * 100)

------------------
98

If you want an integer between 1 and 100, convert to an integer and add one to the result. This function is useful inside a WHILE loop when you want to generate some fast, random data.

You may optionally provide a seed value to the random number generator by specifying it inside the parentheses.

Summary

Local variables are declared with the DECLARE statement. Local variables are used as loop counters and to hold data values temporarily. They are prefaced by one @ sign. Global variables have two @ signs before their name. Global variables are set by the server and may be read by the client.

The IF statement allows conditional execution. The WHILE statement executes a block of SQL statements until the WHILE condition tests false. RETURN will immediately exit the batch. The RAND() function generates a random number between 0 and 1.