Loops in SQL

Repeat a code block.

Introduction

Loops are a fundamental programming construct that allows you to execute a block of code repeatedly based on a specified condition. In SQL Server, loops are typically used within stored procedures or scripts to perform repetitive tasks, such as processing rows in a table or performing operations until a certain condition is met. This chapter will explore the different types of loops in MS SQL Server.

Why Use Loops?

Loops can be particularly useful in scenarios such as:

  • Iterating over rows in a result set to perform calculations or updates.
  • Processing data in bulk, such as adjusting customer statuses based on business rules.
  • Implementing retry logic for operations that may fail.

Types of Loops in SQL Server

SQL Server provides several looping constructs:

  1. WHILE Loop
  2. FOR Loop
  3. CURSOR Loop

We will focus primarily on the WHILE loop, as it is the most commonly used loop in SQL.

  1. WHILE Loop

The WHILE loop continues to execute a block of code as long as a specified condition evaluates to true. The basic syntax is:

Syntax

WHILE condition
BEGIN
    -- SQL statements
END

Example:

Let's consider a scenario where we need to apply a discount to customers who have been with us for more than a year. We will iterate through a list of customers, checking their join date, and apply a discount if they qualify.

We want to iterate through the Customers table and give a 10% discount to customers who joined before 2023.

DECLARE @CustomerID INT;
DECLARE @JoinDate DATE;

-- Start with the first customer
SET @CustomerID = 1;

WHILE @CustomerID <= (SELECT COUNT(*) FROM Customers)
BEGIN
    -- Get the JoinDate for the current customer
    SELECT @JoinDate = date_created
    FROM Customers
    WHERE customer_id = @CustomerID;

    -- Apply discount if the join date is before 2023
    IF @JoinDate < '2023-01-01'
    BEGIN
        UPDATE Customers
        SET Discount = 0.10
        WHERE customer_id = @CustomerID;
    END

    -- Move to the next customer
    SET @CustomerID = @CustomerID + 1;
END

Explanation of the Code:

  1. Variable Declarations: We declare @CustomerID and @JoinDate to hold the current customer ID and their join date.

  2. Initialize Customer ID: We start with the first customer by setting @CustomerID = 1.

  3. WHILE Loop: The loop continues as long as @CustomerID is less than or equal to the total number of customers.

  4. Retrieve Join Date: We select the date_created for the current customer using their customer_id.

  5. Conditional Update: If the date_created is before January 1, 2023, we update the Discount to 10%.

  6. Increment Customer ID: We move to the next customer by incrementing @CustomerID.

  7. FOR Loop (Using CURSOR)

While SQL Server does not have a built-in FOR loop, you can achieve similar functionality using a cursor. A cursor allows you to fetch a row from a result set, process it, and then fetch the next row.

Using a Cursor in Customer Management System

Let’s see how to apply the same discount logic using a cursor.

Example:

DECLARE @CustomerID INT;
DECLARE @JoinDate DATE;

-- Declare a cursor to iterate through customers
DECLARE CustomerCursor CURSOR FOR
SELECT customer_id, date_created
FROM Customers;

OPEN CustomerCursor;

FETCH NEXT FROM CustomerCursor INTO @CustomerID, @JoinDate;

WHILE @@FETCH_STATUS = 0
BEGIN
    -- Apply discount if the join date is before 2023
    IF @JoinDate < '2023-01-01'
    BEGIN
        UPDATE Customers
        SET Discount = 0.10
        WHERE customer_id = @CustomerID;
    END

    -- Fetch the next customer
    FETCH NEXT FROM CustomerCursor INTO @CustomerID, @JoinDate;
END

CLOSE CustomerCursor;
DEALLOCATE CustomerCursor;

Explanation of the Code:

  1. Cursor Declaration: We declare a cursor named CustomerCursor to select customer_id and date_created from the Customers table.

  2. Open the Cursor: We open the cursor to begin fetching rows.

  3. Fetch Initial Row: The FETCH NEXT statement retrieves the first row from the cursor into our declared variables.

  4. WHILE Loop: The loop continues as long as @@FETCH_STATUS returns 0, indicating a successful fetch.

  5. Conditional Update: Similar to the previous example, we update the discount if the join date qualifies.

  6. Fetch Next Row: We fetch the next row from the cursor to continue processing.

  7. Close and Deallocate Cursor: After processing, we close and deallocate the cursor to free up resources.

  8. Using Recursive Common Table Expressions (CTEs)

In certain situations, you might want to perform operations that require recursion, such as processing hierarchical data. While not a loop in the traditional sense, a Recursive CTE can achieve similar results.

Conclusion

Loops in MS SQL Server, particularly the WHILE loop and cursors, provide powerful mechanisms to perform repetitive tasks and manage data effectively. In the context of a customer management system, these constructs allow you to efficiently iterate through customer records, apply business logic, and enforce data integrity.

Asked in Interview

  • Does SQL support loops like traditional programming languages (e.g., for and while loops)?
  • How does a WHILE loop work in SQL, and can you give an example?
  • What is a cursor in SQL, and how does it allow looping through result sets?
  • What are the drawbacks of using cursors in SQL?
  • How does recursion work in SQL using Common Table Expressions (CTEs)?
  • In which scenarios would you consider using loops in SQL?
  • When would you use a WHILE loop versus a cursor in SQL?
  • How can you optimize loop performance in SQL?
  • SQL doesn't have a FOR loop like traditional programming languages. How would you simulate a FOR loop in SQL?
  • Is it possible to use nested loops in SQL? If yes, give an example.