WHILE Statements in SQL

Previously I've spoken about control of flow statements in SQL (see the following: IF...ELSE, GOTO).  Today I will talk about another control of flow statement: the WHILE statement.

A WHILE statement allows you to specify a statement to check and then SQL statement(s) to run while that expression is still true.

The general outline for a WHILE statement is as follows:

WHILE Boolean_expression
{ sql_statement }

• A boolean expression is one that is either true or false
• The SQL statement(s) are the statements to run while the WHILE statement is still true
• If your SQL statement is more than one statement, you will have to begin and end your WHILE statement with BEGIN and END, as seen below.

WHILE Boolean_expression

BEGIN

SQL Statement Number 1

SQL Statement Number 2

END

Following is an example of when a WHILE statement may be useful:

WHILE (SELECT count(*) FROM Customers Where Balance < 50) > 0

BEGIN

UPDATE Customers

SET Balance = Balance * 2

Where Balance < 50

END

The above will update the balance of a customer to their balance times 2 until that balance is above $50.  For example, if we have a customer whose balance is $5, it will go through the WHILE statement 4 times until the balance is $80.

Keep in mind that when WHILE statements are used, it is possible to run into an infinite loop (a loop that never stops) if you aren't incrementing your values correctly.  Using the same query, if I change it a little bit, I can get an infinite loop:

WHILE (SELECT count(*) FROM Customers Where Balance > 50) > 0

BEGIN

UPDATE Customers

SET Balance = Balance * 2

Where Balance > 50

END

The above will never stop if I have one customer that has a balance over $50.  Because they are over $50, our boolean expression is and will always be true.

To read up more on WHILE statements, go here: WHILE