Previous | Table of Contents | Next |
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. Its 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, its 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 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.
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.
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.
Previous | Table of Contents | Next |