SQL loop statement while introduces the instance

Declare @ I int
Set @ I = 1
While @ I <30
Begin
Insert into test (userid) values (@ I)
Set @ I = @ I + 1
End
---------------

While Condition
Begin
Perform operations
Set @ I = @ I + 1
End

WHILE
Set the conditions for repeated execution of SQL statements or statement blocks. If the specified condition is true, the statement is executed repeatedly. You can use the BREAK and CONTINUE keywords to control the execution of statements in the while loop within the loop.

Syntax
WHILE Boolean_expression
{SQL _statement | statement_block}
[BREAK]
{SQL _statement | statement_block}
[CONTINUE]

Parameters
Boolean_expression

Returns a TRUE or FALSE expression. If a Boolean expression contains a SELECT statement, you must enclose the SELECT statement in parentheses.

{SQL _statement | statement_block}

Group statements defined by statement blocks. To define statement blocks, use the control flow keywords BEGIN and END.

BREAK

This causes the exit from the WHILE loop at the innermost layer. Execute any statement that appears after the END keyword. The END keyword is the END mark of the loop.

CONTINUE

Execute the WHILE loop again and ignore any statements after the CONTINUE keyword.

Note
If two or more WHILE loops are nested, the BREAK of the inner layer will exit the next outer loop. First, run all the statements after the inner loop ends, and then run the next outer loop again.

Example
A. Use BREAK and CONTINUE in nested IF... ELSE and WHILE
In the following example, if the average price is less than $30, the WHILE loop will double the price and select the highest price. If the maximum price is less than or equal to $50, the while loop restarts and doubles the price again. This cycle continues to double the price until the maximum price exceeds $50, then exit the WHILE loop and print a message.

USE pubs
GO
WHILE (select avg (price) FROM titles) <$30
BEGIN
UPDATE titles
SET price = price * 2
Select max (price) FROM titles
IF (select max (price) FROM titles)> $50
BREAK
ELSE
CONTINUE
END
PRINT 'too much for the market to bear'

B. Use the WHILE
The following WHILE structure is part of the process named count_all_rows. In the following example, the WHILE structure tests the return value of the function @ FETCH_STATUS for the cursor. Because @ FETCH_STATUS may return-2,-1, or 0, all cases should be tested. If a row is deleted from the cursor results after execution of this stored procedure, the row is skipped. After successfully extracting (0), the SELECT statement inside the BEGIN... END loop is executed.

USE pubs
DECLARE tnames_cursor CURSOR
FOR
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
OPEN tnames_cursor
DECLARE @ tablename sysname
-- SET @ tablename = 'author'
Fetch next from tnames_cursor INTO @ tablename
WHILE (@ FETCH_STATUS <>-1)
BEGIN
IF (@ FETCH_STATUS <>-2)
BEGIN
SELECT @ tablename = RTRIM (@ tablename)
EXEC ('select' + @ tablename + ''' = count (*) from'
+ @ Tablename)
PRINT''
END
Fetch next from tnames_cursor INTO @ tablename
END
CLOSE tnames_cursor
DEALLOCATE tnames_cursor

Declare @ I int
Declare @ quxian varchar (2000), @ city varchar (2000), @ sheng varchar (2000), @ hot int
Set @ I = 1
While @ I <30
Begin
Set @ quxian = 'xihu'
Set @ city = 'hangzhou'
Set @ sheng = 'zhejiang'
Set @ hot = @ I
Insert into address (quxian, city, sheng, hot) values (@ quxian, @ city, @ sheng, @ hot)
Print @ I
Set @ I = @ I + 1
End

This article from the CSDN blog, reproduced please indicate the source: http://blog.csdn.net/txl816/archive/2009/07/24/4376274.aspx