SQL Server: WHILE LOOP

In this post explains how to use the WHILE LOOP in SQL Server (Transact-SQL) with syntax and examples.

Description

In SQL Server, you use a WHILE LOOP when you are not sure how many times you will execute the loop body and the loop body may not execute even once.

Syntax

The syntax for the WHILE LOOP in SQL Server (Transact-SQL) is:

WHILE condition
BEGIN
   {...statements...}
END;

Parameters or Arguments

condition

The condition is tested each pass through the loop. If condition evaluates to TRUE, the loop body is executed. If condition evaluates to FALSE, the loop is terminated.

statements

The statements of code to execute each pass through the loop.

Note

• You would use a WHILE LOOP statement when you are unsure of how many times you want the loop body to execute.
• Since the WHILE condition is evaluated before entering the loop, it is possible that the loop body may not execute even once.
• See also the BREAK statement to exit from the WHILE LOOP early.
• See also the CONTINUE statement to restart the WHILE LOOP from the beginning.

Example

Let's look at an example that shows how to use a WHILE LOOP in SQL Server (Transact-SQL).

For example:

DECLARE @site_value INT;
SET @site_value = 0;

WHILE @site_value = 10
BEGIN
   PRINT 'Inside WHILE LOOP on AODBA.com';
   SET @site_value = @site_value + 1;
END;

PRINT 'Done WHILE LOOP on AODBA.com';
GO

In this WHILE LOOP example, the loop would terminate once the @site_value exceeded 10 as specified by:

WHILE @site_value = 10

The WHILE LOOP will continue while @site_value = 10. And once @site_value is > 10, the loop will terminate.

You can also use a WHILE LOOP in a cursor.

For example:

DECLARE contacts_cursor CURSOR FOR
SELECT contact_id, website_id
FROM contacts;

OPEN contacts_cursor;
FETCH NEXT FROM contacts_cursor;

WHILE @@FETCH_STATUS = 0
   BEGIN
      FETCH NEXT FROM contacts_cursor;
      PRINT 'Inside WHILE LOOP on AODBA.com';
   END;
PRINT 'Done WHILE LOOP on AODBA.com';

CLOSE contacts_cursor;
DEALLOCATE contacts_cursor;
GO

In this WHILE LOOP example, the loop would terminate once the @@FETCH_STATUS no longer equals 0, as specified by:

WHILE @@FETCH_STATUS = 0