T-SQL 101: 100 Creating loops in T-SQL with WHILE

Most programming languages have some way of forming loops. Now some of them have a variety. For example, a language like Basic or Excel had a For Next loop, a Do While loop, a While Until loop, etc.

In T-SQL, while you could conceivably construct loops manually by using a GOTO statement, no-one does that and it would be considered a bad idea.

Instead, we form all the loops we need by using a WHILE statement. It can be used to create all the variety of loops mentioned above.

Here’s an example of the equivalent of a simple For Next loop:

DECLARE @Counter int = 1;
WHILE @Counter < 10
BEGIN
    -- Do something here
    SET @Counter += 1;
END;

The condition can anything that’s a logical value in T-SQL, including combinations of AND and OR.

After the WHILE, you need a single statement. As I normally do with any of these types of statements, I used a BEGIN and END as a wrapper, so that anything between them becomes a single statment.

If you want the equivalent of a While Until loop, that’s easy:

WHILE @Counter < (SELECT COUNT(1) FROM SomeTable)
BEGIN
    -- Do something
    SET @Counter += 1;
END;

Another common option is that you might want to do something until a condition occurs, like a Do Until loop, and break out of the loop when the condition is met. Here’s an example:

WHILE (1 = 1)
BEGIN
    DELETE TOP(4000)
    FROM dbo.Transactions
    WHERE TransactionDate < '20080101';

    IF @@ROWCOUNT < 1000 BREAK;
END;

Here, I’ve made the condition something that’s an infinite loop. Then I’ve deleted rows from the table 4000 at a time. When we get to the end (i.e., where less than 1000 have been deleted), we use the BREAK statement to exit the loop. We often use something like this (in conjunction with appropriate indexing), to avoid escalating to table locks while deleting.

You can also nest WHILE loops. If you do that, and then use BREAK, it exits the loop where the statement was run, not any outer loops.

Learning T-SQL

It’s worth your while becoming proficient in SQL. If you’d like to learn a lot about T-SQL in a hurry, our Writing T-SQL Queries for SQL Server course is online, on-demand, and low cost.

2025-01-27