SQL while loop

DECLARE @i int
Set @i=1
While @i<30
Begin
INSERT INTO Test (userid) VALUES (@i)
Set @[email protected]+1
End
---------------

While condition
Begin
Perform actions
Set @[email protected]+1
End

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.

Use pubs
GO
while (SELECT AVG, from titles) < $
BEGIN
UPDATE titles
SET Price = Price * 2
SELECT MAX (price) from titles
IF (SELECT MAX from titles) > $
Break
ELSE
CONTINUE
END
PRINT ' Too much

B. A while structure that uses while
with a cursor 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.

Use pubs
DECLARE tnames_cursor cursor
for
     SELECT table_name 
      from INFORMATION_SCHEMA. TABLES
OPEN tnames_cursor
DECLARE @tablename sysname
--set @tablename = ' authors '
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 to @tablename
END
CLOSE tnames_cursor
deallocate tnames_ Cursor

DECLARE @i int
DECLARE @quxian varchar, @city varchar, @sheng varchar, @hot int
Set @i=1
While @i<30
Begin
Set @quxian = ' West Lake '
Set @city = ' Hangzhou '
Set @sheng = ' Zhejiang '
Set @[email protected]
Insert into address (quxian,city,sheng,hot) VALUES (@quxian, @city, @sheng, @hot)
Print @i
Set @[email protected]+1
End

SQL while loop