SQL Cursors: Retrieving Data Row by Row

SQL cursors are powerful tools that allow developers to process data row by row, offering granular control over result sets. While set-based operations are generally preferred in SQL for their efficiency, cursors provide a way to perform row-by-row operations when necessary. In this comprehensive guide, we'll dive deep into SQL cursors, exploring their functionality, types, and practical applications.

Table of Contents

Understanding SQL Cursors

🔍 A cursor in SQL is a database object that allows you to traverse through the rows of a result set one at a time. It's like a pointer that can be moved from one row to another, enabling you to perform operations on individual rows.

Cursors are particularly useful when you need to:

Process complex business logic that requires row-by-row handling
Update or delete records based on conditions that involve multiple rows
Perform operations that cannot be easily accomplished with set-based operations

Types of Cursors

There are four main types of cursors in SQL:

Static Cursors: Create a temporary copy of the data, allowing you to navigate freely without being affected by changes made to the underlying data.
Dynamic Cursors: Reflect all changes made to the underlying data, providing the most up-to-date view of the data.
Keyset Cursors: A hybrid between static and dynamic cursors, reflecting changes to existing rows but not showing newly inserted rows.
Forward-Only Cursors: Can only move forward through the result set, typically the fastest and least resource-intensive.

Cursor Operations

The basic operations you can perform with a cursor are:

DECLARE: Define the cursor and its associated SELECT statement.
OPEN: Execute the SELECT statement and populate the result set.
FETCH: Retrieve data from the cursor, moving to the next row.
CLOSE: Close the cursor when you're done using it.
DEALLOCATE: Remove the cursor definition and free up system resources.

Let's dive into some practical examples to see how these operations work in real-world scenarios.

Example 1: Basic Cursor Usage

Let's start with a simple example using a table of employees. We'll use a cursor to iterate through the employees and print their names and salaries.

First, let's create and populate our sample table:

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    Salary DECIMAL(10, 2)
);

INSERT INTO Employees (EmployeeID, FirstName, LastName, Salary)
VALUES 
(1, 'John', 'Doe', 50000),
(2, 'Jane', 'Smith', 60000),
(3, 'Mike', 'Johnson', 55000),
(4, 'Emily', 'Brown', 62000),
(5, 'David', 'Wilson', 58000);

Now, let's use a cursor to iterate through this table:

DECLARE @EmployeeID INT, @FirstName VARCHAR(50), @LastName VARCHAR(50), @Salary DECIMAL(10, 2);

DECLARE employee_cursor CURSOR FOR
SELECT EmployeeID, FirstName, LastName, Salary
FROM Employees;

OPEN employee_cursor;

FETCH NEXT FROM employee_cursor INTO @EmployeeID, @FirstName, @LastName, @Salary;

WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT 'Employee: ' + @FirstName + ' ' + @LastName + ', Salary: $' + CAST(@Salary AS VARCHAR(20));
    FETCH NEXT FROM employee_cursor INTO @EmployeeID, @FirstName, @LastName, @Salary;
END

CLOSE employee_cursor;
DEALLOCATE employee_cursor;

This script will produce the following output:

Employee: John Doe, Salary: $50000.00
Employee: Jane Smith, Salary: $60000.00
Employee: Mike Johnson, Salary: $55000.00
Employee: Emily Brown, Salary: $62000.00
Employee: David Wilson, Salary: $58000.00

In this example, we:

Declared variables to hold each row's data
Declared a cursor with a SELECT statement
Opened the cursor
Used a WHILE loop to fetch and process each row
Closed and deallocated the cursor

Example 2: Updating Data with a Cursor

Now, let's use a cursor to give a 10% raise to employees whose salary is below $60,000.

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

DECLARE salary_update_cursor CURSOR FOR
SELECT EmployeeID, Salary
FROM Employees
WHERE Salary < 60000;

OPEN salary_update_cursor;

FETCH NEXT FROM salary_update_cursor INTO @EmployeeID, @Salary;

WHILE @@FETCH_STATUS = 0
BEGIN
    UPDATE Employees
    SET Salary = @Salary * 1.1
    WHERE EmployeeID = @EmployeeID;

    PRINT 'Updated salary for EmployeeID ' + CAST(@EmployeeID AS VARCHAR(10)) + 
          ' from $' + CAST(@Salary AS VARCHAR(20)) + 
          ' to $' + CAST(@Salary * 1.1 AS VARCHAR(20));

    FETCH NEXT FROM salary_update_cursor INTO @EmployeeID, @Salary;
END

CLOSE salary_update_cursor;
DEALLOCATE salary_update_cursor;

-- Display updated salaries
SELECT EmployeeID, FirstName, LastName, Salary
FROM Employees;

This script will produce output similar to:

Updated salary for EmployeeID 1 from $50000.00 to $55000.00
Updated salary for EmployeeID 3 from $55000.00 to $60500.00
Updated salary for EmployeeID 5 from $58000.00 to $63800.00

And the final SELECT statement will show:

EmployeeID	FirstName	LastName	Salary
1	John	Doe	55000.00
2	Jane	Smith	60000.00
3	Mike	Johnson	60500.00
4	Emily	Brown	62000.00
5	David	Wilson	63800.00

In this example, we used a cursor to:

Select employees with salaries below $60,000
Update each selected employee's salary
Print information about each update

Example 3: Using a Cursor with a Temporary Table

Sometimes, you might want to use a cursor to populate a temporary table. Let's create a report of employees and their salary categories.

-- Create a temporary table to store our results
CREATE TABLE #EmployeeSalaryReport (
    EmployeeID INT,
    FullName VARCHAR(100),
    Salary DECIMAL(10, 2),
    SalaryCategory VARCHAR(20)
);

DECLARE @EmployeeID INT, @FirstName VARCHAR(50), @LastName VARCHAR(50), @Salary DECIMAL(10, 2), @SalaryCategory VARCHAR(20);

DECLARE employee_report_cursor CURSOR FOR
SELECT EmployeeID, FirstName, LastName, Salary
FROM Employees;

OPEN employee_report_cursor;

FETCH NEXT FROM employee_report_cursor INTO @EmployeeID, @FirstName, @LastName, @Salary;

WHILE @@FETCH_STATUS = 0
BEGIN
    -- Determine salary category
    SET @SalaryCategory = 
        CASE 
            WHEN @Salary < 55000 THEN 'Low'
            WHEN @Salary BETWEEN 55000 AND 60000 THEN 'Medium'
            ELSE 'High'
        END;

    -- Insert into temporary table
    INSERT INTO #EmployeeSalaryReport (EmployeeID, FullName, Salary, SalaryCategory)
    VALUES (@EmployeeID, @FirstName + ' ' + @LastName, @Salary, @SalaryCategory);

    FETCH NEXT FROM employee_report_cursor INTO @EmployeeID, @FirstName, @LastName, @Salary;
END

CLOSE employee_report_cursor;
DEALLOCATE employee_report_cursor;

-- Display the report
SELECT * FROM #EmployeeSalaryReport;

-- Clean up
DROP TABLE #EmployeeSalaryReport;

This script will produce a result set like this:

EmployeeID	FullName	Salary	SalaryCategory
1	John Doe	55000.00	Medium
2	Jane Smith	60000.00	High
3	Mike Johnson	60500.00	High
4	Emily Brown	62000.00	High
5	David Wilson	63800.00	High

In this example, we:

Created a temporary table to store our report
Used a cursor to iterate through all employees
Determined a salary category for each employee
Inserted the processed data into our temporary table
Displayed the final report

Performance Considerations

⚠️ While cursors are powerful, they can be performance-intensive, especially when dealing with large datasets. Here are some tips to optimize cursor usage:

Use set-based operations when possible: Cursors are row-by-row operations and are generally slower than set-based operations.
Choose the right cursor type: Forward-only cursors are the fastest and least resource-intensive.
Limit the result set: Use WHERE clauses in your cursor declaration to limit the number of rows processed.
Close and deallocate cursors: Always close and deallocate cursors when you're done using them to free up resources.
Consider alternatives: Sometimes, a while loop with a TOP clause can be more efficient than a cursor.

Conclusion

SQL cursors provide a powerful way to process data row by row, offering fine-grained control over your data operations. While they should be used judiciously due to performance considerations, they can be invaluable for complex data processing tasks that require row-by-row handling.

In this guide, we've explored the basics of SQL cursors, their types, and how to use them in practical scenarios. We've seen how to iterate through data, update records, and create reports using cursors. By understanding when and how to use cursors effectively, you can add a powerful tool to your SQL toolkit, enabling you to tackle complex data processing tasks with confidence.

Remember, the key to mastering SQL cursors is practice. Try implementing these examples in your own database environment, and experiment with different scenarios to deepen your understanding of this powerful SQL feature.