How to Loop Through a Set of Records in SQL

1. Overview

Looping through a set of records is a common requirement when working with SQL, especially when performing operations on individual rows or columns.

While SQL is inherently a declarative language, meaning it focuses on what data to retrieve, rather than how to retrieve it, there are scenarios where we might need to perform iterative operations. SQL provides a few methods to help us loop through records in database management systems like MySQL, SQL Server, and PostgreSQL.

In this tutorial, we’ll explore various methods to loop through records in SQL, focusing on different database systems. We’ll use the Baeldung University schema for code examples throughout the tutorial.

2. Why Looping in SQL Should Be Avoided When Possible

SQL engines are optimized for set-based operations, which means that processing an entire dataset at once is typically faster than handling individual records sequentially. It’s a best practice to structure queries in ways that enable the SQL engine to handle all records simultaneously.

However, some tasks, such as those involving stored procedures or external API calls, require row-by-row processing. While knowing when and how to loop through records can improve database management effectiveness, we should use these approaches sparingly for performance reasons.

3. Using Cursors

One of the most common ways to loop through records in SQL is using cursors. Cursors enable us to retrieve rows from a result set sequentially, enabling row-by-row processing.

Let’s explore how to create and work with cursors in popular SQL variations.

3.1. Using Cursors in SQL Server

This example demonstrates how to declare, open, and use a cursor to loop through records in SQL Server:

DECLARE @StudentID INT;
DECLARE @StudentName NVARCHAR(100);

-- Declare the cursor
DECLARE StudentCursor CURSOR FOR
SELECT id, name FROM Student;

-- Open the cursor
OPEN StudentCursor;

-- Fetch the first row
FETCH NEXT FROM StudentCursor INTO @StudentID, @StudentName;

-- Loop through each row
WHILE @@FETCH_STATUS = 0
BEGIN
    -- Process each row
    PRINT 'Processing Student: ' + @StudentName;
    
    -- Fetch the next row
    FETCH NEXT FROM StudentCursor INTO @StudentID, @StudentName;
END;

-- Close and deallocate the cursor
CLOSE StudentCursor;
DEALLOCATE StudentCursor;

In this example, we declare the cursor StudentCursor to loop through each record in the Student table. The cursor retrieves the id and name columns for each student, storing them in the @StudentID and @StudentName variables.

Once the cursor is opened, we use a WHILE loop to fetch and process each row one by one. Inside the loop, the @StudentID and @StudentName variables enable us to access each student’s details and perform actions, such as displaying the student’s name with a PRINT statement.

After processing all rows, we close the cursor and deallocate it to free up resources.

3.2. Using Cursors in MySQL

MySQL also supports cursors, but they’re typically used within stored procedures.

Let’s look at an example using the Student table:

DELIMITER //

CREATE PROCEDURE ProcessStudents()
BEGIN
    DECLARE done INT DEFAULT 0;
    DECLARE studentID INT;
    DECLARE studentName VARCHAR(100);
    
    -- Declare the cursor
    DECLARE studentCursor CURSOR FOR SELECT id, name FROM Student;
    
    -- Declare a handler for cursor termination
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
    
    -- Open the cursor
    OPEN studentCursor;
    
    -- Fetch rows and loop through
    REPEAT
        FETCH studentCursor INTO studentID, studentName;
        
        IF NOT done THEN
            -- Process each row
            SELECT CONCAT('Processing Student: ', studentName);
        END IF;
        
    UNTIL done END REPEAT;

    -- Close the cursor
    CLOSE studentCursor;
END //

DELIMITER ;

In this example, we declare the cursor studentCursor to loop through each record in the Student table. Once the cursor is opened, we use a REPEAT…UNTIL loop to fetch and process each row sequentially.

3.3. Using Cursors in PostgreSQL

PostgreSQL also supports cursors. They’re usually declared and opened within functions.

Let’s look at an example using the Student table:

DO $$
DECLARE
    studentRecord RECORD;
BEGIN
    -- Declare and open the cursor
    FOR studentRecord IN SELECT id, name FROM Student LOOP
        -- Process each row
        RAISE NOTICE 'Processing Student: %', studentRecord.name;
    END LOOP;
END $$;

PostgreSQL’s FOR loop enables us to iterate directly over each row returned by the SELECT statement from the Student table.

4. Looping With WHILE Loops

Another way to iterate over records in SQL is with a WHILE loop, especially when using increment-based iteration. This approach is practical when fetching records by their position or unique identifier, such as id.

4.1. Using a WHILE Loop in SQL Server

Let’s look at an example of using WHILE loops in SQL Server:

DECLARE @Counter INT = 1;
DECLARE @MaxID INT = (SELECT MAX(id) FROM Student);

WHILE @Counter <= @MaxID
BEGIN
    -- Process each row based on the counter value
    SELECT name FROM Student WHERE id = @Counter;
    
    -- Increment the counter
    SET @Counter = @Counter + 1;
END;

The WHILE loop iterates through each record in the Student table based on the id field.

4.2. Using a WHILE Loop in MySQL

In MySQL, WHILE loops are often used within stored procedures.

Let’s loop through each student record by id:

DELIMITER //

CREATE PROCEDURE ProcessStudents()
BEGIN
    DECLARE counter INT DEFAULT 1;
    DECLARE maxID INT;
    
    -- Get the maximum id from the Student table
    SELECT MAX(id) INTO maxID FROM Student;
    
    WHILE counter <= maxID DO
        -- Process each row based on the counter value
        SELECT name FROM Student WHERE id = counter;
        
        -- Increment the counter
        SET counter = counter + 1;
    END WHILE;
END //

DELIMITER ;

We define the stored procedure ProcessStudents and then declare a counter variable initialized to 1 and a maxID variable to store the maximum id. The WHILE loop iterates through each student by id, retrieving the name of each whose id matches the counter.

4.3. Using a WHILE Loop in PostgreSQL

In PostgreSQL, we can use WHILE loops within PL/pgSQL functions or anonymous DO blocks:

DO $$
DECLARE
    counter INT := 1;
    maxID INT;
BEGIN
    -- Get the maximum id from the Student table
    SELECT MAX(id) INTO maxID FROM Student;
    
    WHILE counter <= maxID LOOP
        -- Process each row based on the counter value
        PERFORM name FROM Student WHERE id = counter;
        
        -- Increment the counter
        counter := counter + 1;
    END LOOP;
END $$;

We use an anonymous DO block to define a PL/pgSQL code block. The WHILE loop iterates through each record by id, using PERFORM to fetch the student name for demonstration. The counter variable increments by 1 in each iteration, until it reaches maxID.

5. Using Temporary Tables

Another effective method of looping through records is by using temporary tables. We can insert the results of a query into a temporary table, and then loop through that table.

Temporary tables can be useful for storing intermediate data we want to iterate, especially when performing complex operations on each row or passing data between the different parts of queries.

5.1. Using Temporary Tables in SQL Server

Let’s look at an example of using temporary tables in SQL Server:

-- Create a temporary table
CREATE TABLE #TempStudent (id INT, name NVARCHAR(100));

-- Insert data into the temporary table
INSERT INTO #TempStudent (id, name)
SELECT id, name FROM Student;

DECLARE @Counter INT = 1;
DECLARE @MaxRow INT = (SELECT COUNT(*) FROM #TempStudent);

WHILE @Counter <= @MaxRow
BEGIN
    -- Process each row based on the counter value
    SELECT name FROM #TempStudent
    WHERE id = (SELECT id FROM #TempStudent ORDER BY id OFFSET @Counter - 1 ROWS FETCH NEXT 1 ROWS ONLY);

    -- Increment the counter
    SET @Counter = @Counter + 1;
END;

-- Drop the temporary table after processing
DROP TABLE #TempStudent;

In this example, we’re creating the temporary table #TempStudent and populating it with data from the Student table. Then, we use a WHILE loop to iterate through each row while using ORDER BY with OFFSET and FETCH to retrieve the row based on the counter.

5.2. Using Temporary Tables in MySQL

In MySQL, we can use a temporary table with a WHILE loop inside a stored procedure to iterate over records:

DELIMITER //

CREATE PROCEDURE ProcessTempTable()
BEGIN
    DECLARE counter INT DEFAULT 1;
    DECLARE maxRow INT;

    -- Create a temporary table
    CREATE TEMPORARY TABLE TempStudent AS
    SELECT id, name FROM Student;

    -- Get the total number of rows in the temporary table
    SELECT COUNT(*) INTO maxRow FROM TempStudent;

    WHILE counter <= maxRow DO
        -- Process each row based on the counter value
        SELECT name FROM TempStudent LIMIT counter - 1, 1;

        -- Increment the counter
        SET counter = counter + 1;
    END WHILE;

    -- Drop the temporary table after processing
    DROP TEMPORARY TABLE TempStudent;
END //

DELIMITER ;

We’re creating a temporary table and then counting the rows in TempStudent to know how many times to loop. In each iteration, we use LIMIT to fetch the row that matches the counter value.

5.3. Using Temporary Tables in PostgreSQL

In PostgreSQL, we can use a temporary table with a loop inside a PL/pgSQL DO block or function:

DO $$
DECLARE
    counter INT := 1;
    maxRow INT;
BEGIN
    -- Create a temporary table
    CREATE TEMP TABLE TempStudent AS
    SELECT id, name FROM Student;

    -- Get the total number of rows
    SELECT COUNT(*) INTO maxRow FROM TempStudent;

    WHILE counter <= maxRow LOOP
        -- Process each row based on the counter value
        PERFORM name FROM TempStudent OFFSET counter - 1 LIMIT 1;

        -- Increment the counter
        counter := counter + 1;
    END LOOP;

    -- Drop the temporary table after processing
    DROP TABLE TempStudent;
END $$;

In this example, we create the temporary table TempStudent and get the row count to control the loop. Each iteration uses OFFSET with LIMIT 1 to fetch each row by its position.

6. Conclusion

Looping through records in SQL is a powerful technique that can be necessary for certain tasks. However, it’s generally advisable to avoid using loops when possible, as SQL is optimized for set-based operations that are more efficient and easier to maintain.

Whenever feasible, leverage set-based approaches to enhance performance and reduce complexity. Finally, when using these methods, always consider their implications on performance and resource consumption to ensure your SQL code is efficient.