While Loop Examples – SQL Server

WHILE LOOP creates a condition for a SQL statement or statement block to be executed again. As long as the provided condition is true, the execution is repeated. With the use of the BREAK and CONTINUE keywords, the WHILE loop’s statement execution can be managed from within the loop itself.

In simple words, the advantage of using a SQL WHILE loop is that we can execute the SQL statement(s) repeatedly until the results of the stated condition turn out to be false.

Example 1:

DECLARE @iCounter INT = 1
WHILE @iCounter < 100
BEGIN
PRINT ‘The current value is ‘ + CONVERT(VARCHAR, @iCounter)
SET @iCounter = @iCounter + 1
END

Instead of the result set in the grid, the output of the aforementioned execution is written as a message.

Example 2:

DECLARE @iCounter INT = 1
WHILE @iCounter < 10
BEGIN
SELECT @iCounter
SET @iCounter = @iCounter + 1
END

The output of the aforementioned execution is displayed as a result set in the grid since it has a SELECT statement.

Example 3:

DECLARE @value INT;
DECLARE @mod INT;
SET @value = 1;
WHILE @value <= 10
BEGIN
SET @mod = @value % 2
IF @mod = 1
BEGIN
SET @value= @value + 1;
CONTINUE
END
PRINT @value
SET @value = @value + 1;
END;

The script mentioned above will continue its execution until the mod results to 1. The STATEMENT keyword CONTINUE skips every statement that follows its execution and transfers control to the first STATEMENT of the while loop.

Example 4:

DECLARE @value INT;
DECLARE @mod INT;
SET @value = 1;
WHILE @value <= 10
BEGIN
PRINT @value
SET @value = @value + 1;
IF @value = 6
BREAK
END;

The script mentioned above will stop executing when the value reaches to 6. The output of the above execution is limited to 5 only. The while loop is stopped when the BREAK keyword is used, and the next statement takes control.

DECLARE @value INT;
SET @value = 1;
WHILE @value <= 100
BEGIN
PRINT @value
IF @value <= 24
BEGIN
SET @value= @value + 1;
CONTINUE
END
IF @value > 24
BEGIN
BREAK
END
END;