WHILE clause in SQL

In SQL, the WHILE clause is used to create a loop that repeatedly executes a set of SQL statements as long as a specified condition is true. It works similar to the WHILE loop in other programming languages.

SQL Syntax

WHILE (condition)
BEGIN
    -- SQL statements
END

In the syntax:

condition: A Boolean expression. The loop continues as long as this condition is TRUE.
BEGIN...END: Used to define the block of statements to be executed in each iteration.

Example: Let's create a simple example using a WHILE loop. Assume we have a table called EmployeeSalaries with two columns: EmployeeID and Salary. We'll use a loop to simulate increasing each employee's salary by 500.

Step 1: Create the table and insert data

SQL

CREATE TABLE EmployeeSalaries (
    EmployeeID INT PRIMARY KEY,
    Salary DECIMAL(10, 2)
);

Load the data into the EmployeeSalaries table.

SQL

INSERT INTO EmployeeSalaries
VALUES (1, 3000), (2, 4500), (3, 4700), (4, 2500);

Step 2: WHILE loop to increment salaries We want to give employees a raise of 500 until their salary reaches at least 5000.

SQL

DECLARE @EmployeeID INT = 1;
DECLARE @MaxEmployeeID INT = (SELECT MAX(EmployeeID) FROM EmployeeSalaries);

-- Loop through each employee
WHILE @EmployeeID <= @MaxEmployeeID
BEGIN
    -- Check if the salary is less than 5000
    IF (SELECT Salary FROM EmployeeSalaries WHERE EmployeeID = @EmployeeID) < 5000
    BEGIN
        -- Increase salary by 500
        UPDATE EmployeeSalaries
        SET Salary = Salary + 500
        WHERE EmployeeID = @EmployeeID;
    END
    
    -- Move to the next employee
    SET @EmployeeID = @EmployeeID + 1;
END;

-- Check updated records
SELECT * FROM EmployeeSalaries;

Explanation:

We declare two variables: @EmployeeID to track the current employee, and @MaxEmployeeID to store the highest EmployeeID in the table.
The WHILE loop runs as long as @EmployeeID is less than or equal to @MaxEmployeeID.
Inside the loop, we use an IF condition to check if the current employee's salary is less than 5000. If so, we increase their salary by 500 using the UPDATE statement.
The loop moves to the next employee by incrementing @EmployeeID.

The above query returns the following result:

EmployeeID	Salary
1	3500
2	5000
3	5200
4	3000

In this example, the WHILE loop increased every employee's salary by 500.

Previous Next