while Loop

Overview of WHILE statement

The WHILE statement is a control-flow statement that allows you to execute a statement block repeatedly as long as a specified is TRUE.

The following illustrates the syntax of the WHILE statement:

12	WHILE Boolean_expression        { sql_statement | statement_block}

In this syntax:

First, the Boolean_expression is an expression that evaluates to TRUE or FALSE.

Second, sql_statement | statement_block is any Transact-SQL statement or a set of Transact-SQL statements. A statement block is defined using the BEGIN...END statement.

If the Boolean_expression evaluates to FALSE when entering the loop, no statement inside the WHILE loop will be executed.

Inside the WHILE loop, you must change some values to make the Boolean_expression returns FALSE at some points. Otherwise, you will have an indefinite loop.

Note that if the Boolean_expression contains a SELECT statement, it must be enclosed in parentheses.

To exit the current iteration of the loop immediately, you use the BREAK statement. To skip the current iteration of the loop and start the new one, you use the CONTINUE statement.

SQL Server WHILE example

Let’s take an example of using the SQL Server WHILE statement to understand it better.

The following example illustrates how to use the WHILE statement to print out numbers from 1 to 5:

1234567

DECLARE @counter INT = 1; WHILE @counter <= 5BEGIN    PRINT @counter;    SET @counter = @counter + 1;END

In this example:

First, we declared the @counter variable and set its value to one.

Then, in the condition of the WHILE statement, we checked if the @counteris less than or equal to five. If it was not, we printed out the @counter and increased its value by one. After five iterations, the @counter is 6 which caused the condition of the WHILE clause evaluates to FALSE, the loop stopped.

The following shows the output:

12345

12345