Understanding WHILE & REPEAT Loops in SQL with Examples

Introduction

SQL (Structured Query Language) is a powerful programming language used for managing and manipulating relational databases. One of the key features of SQL is its ability to perform repetitive tasks efficiently through the use of loops. In this blog post, we will explore the concept of loops in SQL, their types, and provide complex examples to demonstrate their practical applications.

What are Loops in SQL?

In SQL, loops are used to iterate over a set of data or perform a set of actions repeatedly until a certain condition is met. They allow us to automate repetitive tasks and make our code more efficient. SQL supports different types of loops, including the WHILE loop, the REPEAT loop, and the FOR loop. Let's explore each of these in detail.

The WHILE Loop

The WHILE loop in SQL is used to repeatedly execute a block of code as long as a specified condition is true. It checks the condition before each iteration and stops when the condition becomes false. The basic syntax for a WHILE loop in SQL is as follows:

WHILE condition
BEGIN
    -- Code to be executed
END

Here, the condition is a Boolean expression that determines whether the loop should continue or stop. The code inside the loop will be executed repeatedly until the condition evaluates to false.

Let's consider an example to illustrate the usage of the WHILE loop. Suppose we have a table called "Employees" with columns "EmployeeID" and "Salary". We want to increase the salary of all employees by 10% until the salary exceeds $100,000. We can achieve this using a WHILE loop as shown below:

DECLARE @EmployeeID INT
DECLARE @Salary DECIMAL(10, 2)

SET @EmployeeID = 1

WHILE @EmployeeID <= (SELECT MAX(EmployeeID) FROM Employees)
BEGIN
    SET @Salary = (SELECT Salary FROM Employees WHERE EmployeeID = @EmployeeID)
    
    IF @Salary <= 100000
        UPDATE Employees SET Salary = Salary * 1.1 WHERE EmployeeID = @EmployeeID
    
    SET @EmployeeID = @EmployeeID + 1
END

In this example, we declare two variables, @EmployeeID and @Salary, to store the current employee ID and salary. We initialize @EmployeeID to 1 and use it as a counter to iterate over all employees. Inside the loop, we retrieve the salary of the current employee and check if it is less than or equal to $100,000. If so, we update the salary by multiplying it by 1.1 (increasing it by 10%). Finally, we increment the @EmployeeID by 1 to move to the next employee.

The REPEAT Loop

The REPEAT loop, also known as the DO-WHILE loop, is another type of loop in SQL. It repeatedly executes a block of code until a specified condition becomes true. Unlike the WHILE loop, the REPEAT loop checks the condition after each iteration. The basic syntax for a REPEAT loop in SQL is as follows:

REPEAT
    -- Code to be executed
UNTIL condition
END REPEAT

Here, the condition is a Boolean expression that determines whether the loop should continue or stop. The code inside the loop will be executed repeatedly until the condition evaluates to true.

Let's consider an example to demonstrate the usage of the REPEAT loop. Suppose we have a table called "Orders" with columns "OrderID" and "OrderDate". We want to find the latest order date. We can achieve this using a REPEAT loop as shown below:

DECLARE @LatestOrderDate DATE
DECLARE @OrderDate DATE

SET @LatestOrderDate = NULL
SET @OrderDate = (SELECT MIN(OrderDate) FROM Orders)

REPEAT
    SET @LatestOrderDate = @OrderDate
    SET @OrderDate = (SELECT MIN(OrderDate) FROM Orders WHERE OrderDate > @OrderDate)
UNTIL @OrderDate IS NULL
END REPEAT

SELECT @LatestOrderDate AS LatestOrderDate

In this example, we declare two variables, @LatestOrderDate and @OrderDate, to store the latest order date and the current order date. We initialize @LatestOrderDate to NULL and @OrderDate to the minimum order date in the "Orders" table. Inside the loop, we update @LatestOrderDate with the current order date and find the next minimum order date greater than @OrderDate. We repeat this process until @OrderDate becomes NULL, indicating that we have found the latest order date. Finally, we select @LatestOrderDate to display the result.

The FOR Loop

Unlike some other programming languages, SQL does not have a built-in FOR loop. However, we can simulate a FOR loop using a combination of variables and the WHILE loop. The idea is to use a counter variable to iterate over a range of values and perform the desired actions.

Let's consider an example to demonstrate the simulation of a FOR loop in SQL. Suppose we want to print the numbers from 1 to 10. We can achieve this using a WHILE loop and a counter variable as shown below:

DECLARE @Counter INT
SET @Counter = 1

WHILE @Counter <= 10
BEGIN
    PRINT @Counter
    SET @Counter = @Counter + 1
END

In this example, we declare a variable, @Counter, and set its initial value to 1. Inside the WHILE loop, we print the value of @Counter and increment it by 1. The loop continues until @Counter becomes greater than 10.

Conclusion

Loops are an essential part of SQL programming, allowing us to automate repetitive tasks and make our code more efficient. In this blog post, we explored the concept of loops in SQL, including the WHILE loop, the REPEAT loop, and the simulation of the FOR loop. We provided complex examples to demonstrate their practical applications in real-world scenarios.

By mastering the art of using loops in SQL, you can enhance your database management skills and become a more proficient SQL developer. Remember to use loops judiciously and optimize your code for better performance.