SQL - Cursors in SQL

A cursor in SQL is a database object used to process the rows returned by a query one row at a time. Normally, SQL works with a set of rows simultaneously. However, some tasks require individual row-by-row processing. In such situations, a cursor can be used to move through the result set and perform a specific operation on each row.

What Is a Cursor?

When a SQL query returns multiple records, the database generally processes those records as a group. A cursor provides a mechanism to navigate through the result set sequentially.

For example, suppose an Employees table contains the following records:

EmployeeID | EmployeeName | Salary
-----------|--------------|--------
101        | Arun         | 30000
102        | Priya        | 40000
103        | Ravi         | 35000

A normal SQL statement can update all employees at once:

UPDATE Employees
SET Salary = Salary + 5000;

A cursor, on the other hand, can retrieve Arun's record, process it, then move to Priya's record, process it, and finally process Ravi's record.

This makes cursors useful when the processing logic must be applied separately to each row.

How a Cursor Works

A cursor generally follows a sequence of operations:

  1. Declare the cursor.

  2. Define the SQL query associated with the cursor.

  3. Open the cursor.

  4. Fetch a row from the result set.

  5. Process the fetched row.

  6. Move to the next row.

  7. Continue until all required rows are processed.

  8. Close the cursor.

  9. Release or deallocate the cursor.

The exact syntax differs between database systems such as SQL Server, Oracle, and PostgreSQL.

Declaring a Cursor

In SQL Server, a cursor can be declared using the DECLARE CURSOR statement.

DECLARE employee_cursor CURSOR FOR
SELECT EmployeeID, EmployeeName, Salary
FROM Employees;

Here, the cursor is associated with the query that retrieves employee information.

The cursor does not necessarily process the records immediately. It establishes the result set that will be accessed when the cursor is opened.

Opening a Cursor

After declaring the cursor, it must be opened.

OPEN employee_cursor;

Opening the cursor makes the result set available for fetching.

Fetching Data

The FETCH statement retrieves a row from the cursor.

FETCH NEXT FROM employee_cursor;

FETCH NEXT moves the cursor to the next available row and retrieves its values.

Usually, variables are used to store the values retrieved from each row.

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

FETCH NEXT FROM employee_cursor
INTO @EmployeeID, @EmployeeName, @Salary;

The values from the current row are placed into the corresponding variables.

Processing Rows with a Cursor

A cursor is commonly combined with a loop to process every row.

DECLARE employee_cursor CURSOR FOR
SELECT EmployeeID, EmployeeName, Salary
FROM Employees;

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

OPEN employee_cursor;

FETCH NEXT FROM employee_cursor
INTO @EmployeeID, @EmployeeName, @Salary;

WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT @EmployeeName;

    FETCH NEXT FROM employee_cursor
    INTO @EmployeeID, @EmployeeName, @Salary;
END;

CLOSE employee_cursor;
DEALLOCATE employee_cursor;

In this example, the cursor retrieves each employee one by one. The WHILE loop continues as long as another row can be successfully fetched.

Closing a Cursor

After processing is complete, the cursor should be closed.

CLOSE employee_cursor;

Closing the cursor releases the resources associated with the active result set.

Deallocating a Cursor

After closing it, the cursor can be removed from memory.

DEALLOCATE employee_cursor;

A typical cursor lifecycle is therefore:

DECLARE
   ↓
OPEN
   ↓
FETCH
   ↓
PROCESS
   ↓
FETCH NEXT
   ↓
PROCESS
   ↓
CLOSE
   ↓
DEALLOCATE

Why Are Cursors Used?

Cursors are useful when an operation cannot easily be expressed using a single set-based SQL statement.

For example, imagine that every employee needs to receive a different bonus depending on several conditions, and the calculation requires information from other operations for each individual employee. A cursor can retrieve each employee and perform the required processing separately.

Cursors can also be useful when:

  • Each row requires a different calculation.

  • Processing depends on the previous row.

  • External procedures must be called separately for individual records.

  • Complex procedural logic is required.

  • A row-by-row operation is unavoidable.

Cursor vs Normal SQL Query

The major difference is the way data is processed.

A normal SQL statement generally works with multiple rows together:

UPDATE Employees
SET Salary = Salary * 1.10;

This statement increases the salary of all employees by 10 percent in a set-based operation.

A cursor processes records individually:

Employee 101 → Process
Employee 102 → Process
Employee 103 → Process

This row-by-row approach provides greater procedural control but generally requires more processing.

Advantages of Cursors

Cursors provide several advantages in situations where row-by-row processing is genuinely required.

1. Individual row control

Each record can be examined and processed separately.

2. Procedural processing

Complex logic can be applied to each row using variables, conditions, and loops.

3. Sequential processing

Cursors can move through records in a controlled sequence.

4. Useful for complex operations

Some operations are difficult to express using a single SQL statement and may be easier to implement procedurally.

Disadvantages of Cursors

Despite their usefulness, cursors should not be used unnecessarily.

1. Slower performance

Processing thousands or millions of records individually can be considerably slower than using a set-based SQL operation.

2. Higher resource usage

Cursors may consume additional memory and database resources while maintaining their result set and processing state.

3. More complex code

Cursor-based solutions generally require more statements, variables, loops, and cleanup operations.

4. Reduced scalability

A query that works efficiently with a small number of records may become inefficient when the table grows significantly.

Cursor vs Set-Based Processing

Consider the following requirement:

Increase the salary of every employee by 10 percent.

A cursor could process every employee individually:

Employee 1 → Increase salary
Employee 2 → Increase salary
Employee 3 → Increase salary
...

However, a set-based statement can perform the entire operation:

UPDATE Employees
SET Salary = Salary * 1.10;

The second approach is generally simpler and more efficient because SQL databases are designed to process sets of records efficiently.

Therefore, a good SQL development practice is to prefer set-based operations whenever the requirement can be expressed using them. Cursors should generally be considered when row-by-row processing is actually necessary.

Practical Example

Suppose a company wants to examine employees individually and print a message according to their salary.

DECLARE employee_cursor CURSOR FOR
SELECT EmployeeName, Salary
FROM Employees;

DECLARE @Name VARCHAR(100);
DECLARE @Salary DECIMAL(10,2);

OPEN employee_cursor;

FETCH NEXT FROM employee_cursor
INTO @Name, @Salary;

WHILE @@FETCH_STATUS = 0
BEGIN
    IF @Salary >= 50000
        PRINT @Name + ' has a high salary';
    ELSE
        PRINT @Name + ' has a standard salary';

    FETCH NEXT FROM employee_cursor
    INTO @Name, @Salary;
END;

CLOSE employee_cursor;
DEALLOCATE employee_cursor;

Here, each employee is fetched separately. The salary is checked, and a different message is generated based on the value.

Important Points to Remember

A cursor is used to process query results row by row. The basic cursor workflow involves declaration, opening, fetching, processing, closing, and deallocation. Cursors provide detailed control over individual records, but they can be slower and more resource-intensive than set-based SQL operations.

For this reason, cursors should not automatically be used whenever a task involves multiple rows. First, determine whether the same requirement can be accomplished using standard SQL statements such as SELECT, UPDATE, INSERT, DELETE, joins, or other set-based techniques. Use a cursor when sequential or individual-row processing is genuinely required.