SQL Loop Statement While introduction instance

Declare @i intSet @i=1 while @i< -beginInsert  intoTest (UserID)Values(@i)Set @i=@i+1End --------------- whileconditionsbeginPerform ActionsSet @i=@i+1End--how to ask Hovertree.com

While
Sets the criteria for repeating the SQL statement or block of statements. Executes the statement repeatedly whenever the specified condition is true. You can use the break and CONTINUE keywords to control the execution of statements within a loop inside a while loop.

Grammar
While Boolean_expression
{sql_statement | statement_block}
[Break]
{sql_statement | statement_block}
[CONTINUE]

Parameters
Boolean_expression

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

{sql_statement | statement_block}

Transact-SQL statements or groups of statements defined with statement blocks. To define a statement block, use the control flow keyword BEGIN and END.

Break

Causes the exit from the inner-most while loop. Any statements that appear after the end keyword are executed, and the end keyword is the loop end tag.

CONTINUE

Causes the while loop to start executing again, ignoring any statements after the CONTINUE keyword.

Comments
If two or more while loops are nested, a break in the inner layer causes the exit to the next outer loop. Run all statements after the end of the inner loop first, and then the next outer loop to start execution again.

Example
A. In nested IF ... Use break and CONTINUE in ELSE and while
In the following example, if the average price is less than the $30,while cycle, double the price, and then select the most expensive. If the high price is less than or equal to the $50,while cycle restarts and doubles the price again. The loop continually doubles the price until the highest price exceeds $ $, then exits the while loop and prints a message.

 UsepubsGO while(SELECT AVG(Price) fromTitles<$ -BEGIN     UPDATEtitlesSETPrice=Price* 2     SELECT MAX(Price) fromtitlesIF(SELECT MAX(Price) fromTitles>$ -         Break     ELSE        CONTINUEENDPRINT 'Too much for the'/*how to ask Hovertree.com*/

B. Using while in the process with cursors
The while structure below 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 used for the cursor. Because @ @FETCH_STATUS may return – 2,-1, or 0, all cases should be tested. If a row is removed from the cursor results after it starts executing the stored procedure, the row is skipped. Successful extraction (0) will be performed after the BEGIN ... The SELECT statement inside the END loop.

 UsepubsDECLARETnames_cursorCURSOR for     SELECTtable_name frominformation_schema. TABLESOPENtnames_cursorDECLARE @tablenamesysname--SET @tablename = ' authors 'FETCH NEXT  fromTnames_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  fromTnames_cursor into @tablenameENDCLOSEtnames_cursordeallocatetnames_cursorDeclare @i intDeclare @quxian varchar( -),@city varchar( -),@sheng varchar( -),@hot intSet @i=1 while @i< -beginSet @quxian='West Lake'Set @city='Hangzhou'Set @sheng='Zhejiang'Set @hot=@iInsert  intoAddress (Quxian,city,sheng,hot)Values(@quxian,@city,@sheng,@hot)Print @iSet @i=@i+1End/*Hovertree.top*/

Recommendation: http://www.cnblogs.com/roucheng/p/3541165.html

SQL Loop Statement While introduction instance