While Loop In SQL Server

Introduction

 

In this article, we will learn how to use looping in SQL Server. In SQL Server, the For() loop is not used. So, for looping, we will use “WHILE” loop in SQL Server. The While statement is a control-flow statement that allows you to execute a statement block repeatedly or group of statements while a given condition is true. It tests the condition before executing the loop body.

 

The syntax for the While Loop in SQL Server (T-SQL) is,

WHILE <Boolean_Expression>
BEGIN
   <Statements>
END

In the above syntax:

 

The Boolean_Expression is an expression that evaluates to TRUE or FALSE. If the condition evaluates to TRUE, the loop body is executed. If the condition evaluates to FALSE, the loop is terminated.

 

Statements are any T-SQL statement or a set of T-SQL statements to execute each pass through the loop. A statement block is defined using the BEGIN...END statement. Inside the WHILE loop, we must put some condition to make the Boolean_expression returns FALSE at some points. Otherwise, you will have an indefinite loop.

 

To exit from the WHILE LOOP, we use the BREAK statement.

 

To restart the WHILE LOOP from the beginning, we use the CONTINUE statement.

 

Example

 

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

 

For example, 

DECLARE @numValue INT = 0

WHILE @numValue < 5
BEGIN
   SET @numValue = @numValue + 1
   PRINT 'Inside WHILE LOOP ' + CAST(@numValue AS VARCHAR) + ' !!'
END

PRINT 'WHILE LOOP End !!'

Result

 

 

In this above example, the loop would terminate once the @numValue is equal to 5. Here, we checked if the @numValue is less than 5. If it was not, we increased its value by one and printed out the @numValue. After five iterations, the condition of the WHILE clause evaluates to FALSE because the @numValue is 5 and the loop stopped.

 

BREAK Statement

 

The BREAK statement is used when you want to exit from a WHILE LOOP. If there are nested WHILE Loops, the BREAK statement will terminate the innermost WHILE LOOP.

 

The syntax for the BREAK statement in SQL Server (T-SQL) is -

 

BREAK

 

For example, 

DECLARE @numValue INT = 0

WHILE @numValue < 5
BEGIN
    SET @numValue = @numValue + 1

    IF @numValue = 3
    BEGIN
        PRINT 'Use BREAK in LOOP ' + CAST(@numValue AS VARCHAR) + ' !!'
        BREAK
    END

    PRINT 'Inside WHILE LOOP ' + CAST(@numValue AS VARCHAR) + ' !!'
END

PRINT 'WHILE LOOP End !!'

Result

 

 

In this above example, the loop would terminate once the @numValue is equal to 3. Here, if the @numValue is equal to 3, we will break out of the WHILE LOOP.

 

CONTINUE Statement

 

The CONTINUE statement is used when you want a WHILE LOOP to execute again. It will ignore any statements after the CONTINUE statement.

 

The syntax for the COUNTINUE statement in SQL Server (T-SQL) is:

 

CONTINUE

 

For example, 

DECLARE @numValue INT = 0

WHILE @numValue < 5
BEGIN
    SET @numValue = @numValue + 1
    PRINT 'Inside WHILE LOOP ' + CAST(@numValue AS VARCHAR) + ' !!'
    CONTINUE
    IF @numValue = 3 -- This will never executed
    BEGIN
        PRINT 'Use BREAK in LOOP ' + CAST(@numValue AS VARCHAR) + ' !!'
        BREAK
    END
END

PRINT 'WHILE LOOP End !!'

Result

 

 

In this above example, the loop would terminate once the @numValue is equal to 5, because it will ignore any statements after the CONTINUE statement.