SQL Cursors

When working with SQL, there are times when set-based processing is not capable of handling the needs, specifically if you are going to process sequentially. This is where cursors can be beneficial to process the complex logic that may be required when processing row-by-row data. Cursors can help process complex logic, such as generating reports or conditional updates, or even if unique business rules or requirements are to be applied dynamically. If used and understood correctly, cursors are one of the powerful tools in SQL. In this blog, let’s explore the SQL Cursors in detail.

Table of Contents:

• What is a Cursor in SQL?
• Types of Cursors in SQL
• Difference between types of Cursors in SQL
• Why do we need to use Cursors in SQL?
• How to create a Cursor in SQL?
• Life Cycle of Cursor in SQL
• Syntax and Example for Cursors in SQL
• Advantages of Cursors in SQL
• Disadvantages of Cursors in SQL
• Alternative Methods for Cursors in SQL
  • Using Common Table Expressions (CTE) in SQL
  • Using WHILE Loops with Temporary Tables in SQL
• Real-world Examples
• Common Mistakes and Best Practices
• Conclusion

Before getting started with the concepts of cursor, let’s create the Project Status table and use that as an example for the following methods.

CREATE TABLE Pro_det (
    Pro_ID VARCHAR(10),
    Emp_Name VARCHAR(50),
    Role VARCHAR(30),
    Status VARCHAR(20)
);

INSERT INTO Pro_det (Pro_ID, Emp_Name, Role, Status) VALUES
('P001', 'Karan', 'Team Lead', 'Active'),
('P002', 'Isha', 'Developer', 'On Hold'),
('P003', 'Mohit', 'QA Analyst', 'Completed'),
('P004', 'Divya', 'Developer', 'Active'),
('P005', 'Alok', 'Project Manager', 'In Progress');

Select * from Pro_det

This is how the Project Status table looks once it is created.

What is a Cursor in SQL?

In SQL, a Cursor is a database object that is used to manipulate one row at a time from the result set. Cursors support sequential data processing of individual rows that are returned by a standard SQL query, which uses all of the data in a collection. Cursors are typically used when complex logic requires processing row-wise data or when iteration is necessary. Cursors can be declared, opened, fetched, and closed as part of a lifecycle. A cursor can be static, dynamic, and/or forward-only, with various behaviour patterns. If these cursors are overused, then they can affect the performance, so they have to be handled properly.

Master Cursors in SQL – Unlock Advanced SQL Skills Today!

Enroll now and transform your future!

Explore Program

Types of Cursors in SQL

To handle row-by-row processing, SQL supports different types of cursors, each designed for a different purpose.

Implicit Cursor in SQL

The SQL engine automatically creates an implicit cursor in memory for every DML statement (INSERT, UPDATE, or DELETE). Implicit cursors are created by the SQL engine for single‐row DML statements, requiring no explicit code. When a query only returns or affects one row, an implicit cursor is typically used. Implicit cursors are efficient and are frequently used for single-row tasks. Implicit cursors give you limited control over behaviour or processing logic.

Explicit Cursor in SQL

An explicit cursor is a programmer-defined cursor for managing each of the rows returned from a query one at a time. To use an explicit cursor, the programmer must perform a series of specific steps: Declare a cursor, open the cursor, fetch one or more rows into variables, and finally close the cursor. Explicit cursors allow the best row-by-row processing along with custom logic. We can do looping logic in procedural situations that depend on each row’s values. Explicit cursors are good, but they are not as fast as using a set-based approach, and should be used when appropriate.

Static Cursor in SQL 

In SQL terms, a static cursor is a type of cursor that can operate on a snapshot of the result set taken when the cursor is opened. That is, any changes made to the underlying table after the cursor is opened (e.g., INSERTs, UPDATEs, DELETEs) will not be discovered during the life of the cursor. Static cursors are particularly useful for generating reports that are consistent and read-only, where real-time changes are not considered important. Static cursors support scrolling and provide stable performance when the dataset is predictable.

Dynamic Cursor in SQL

A dynamic cursor holds a cursor statement that reflects any changes to the underlying data while the cursor is open. Dynamic cursors are fully sensitive to any INSERT, UPDATE, or DELETE operations by any user or process, and always present the most current version of the data when being traversed in the result set. If you need real-time current data, dynamic cursors provide the right visibility, but in high-transaction environments, they may be variable and/or resource-intensive.

Forward-Only Cursor in SQL

A Forward-Only Cursor is the basic type of cursor in SQL. It can traverse the result set from the first row to the last row, but cannot go backwards. Once a Next operation has been performed on the cursor to read the data, the cursor will fetch one row at a time in sequence and will not scroll. This is a read-only cursor and will not reflect any changes to the result set made after it was opened. It is the most efficient type of cursor in terms of memory and system resource utilisation. A forward-only cursor is great for going fast and only doing a single pass or linear processing of data.

Difference between types of Cursors in SQL

Why do we need to use Cursors in SQL?

Cursors are necessary in SQL when you need to implement row-by-row processing with query results, where a traditional set-based SQL processing structure wouldn’t be effective. SQL is fundamentally designed to operate on a set of data, but there are many real-world operational problems where you need to operate on each row, and some of these operational problems include applying conditional logic, using sequential calculations, invoking external procedures, etc. 

For Example, let’s consider a company that wants to send a personalised email to employees based on their department. In these situations, a cursor will enable you to loop through each row, inspect values, and act accordingly.

Cursors are often used or required in stored procedures, batch jobs, and triggers when procedural control is needed. However, a cursor is resource-intensive and can be slower, so you may want to avoid them unless it is necessary, especially if performance is a concern and you cannot find a proper, efficient set-based alternative.

How to create a Cursor in SQL?

To create a cursor in SQL, we need to follow several steps to define, control, and process data row by row. 

Step 1: Declaration of Cursor

DECLARE cursor_name CURSOR FOR
SELECT column1, column2 FROM table_name WHERE condition;

Step 2: Opening the cursor

OPEN cursor_name;

Step 3: To fetch the data from the cursor

FETCH NEXT FROM cursor_name INTO @variable1, @variable2;

Step 4: To iterate through the result set 

WHILE @@FETCH_STATUS = 0
BEGIN
    -- Logic
    FETCH NEXT FROM cursor_name INTO @variable1, @variable2;
END

Step 5: Close and deallocate the cursor 

CLOSE cursor_name;
DEALLOCATE cursor_name;

Key Parts:

• DECLARE: This command creates the cursor based on a SELECT command.
• OPEN: This command executes the query and places the cursor before the first row.
• FETCH: The FETCH command gets the current row and advances the cursor position.
• @@FETCH_STATUS: This is a system function that checks whether the last call to FETCH was successful.
• CLOSE and DEALLOCATE: These two commands free up the cursor.

Life Cycle of Cursor in SQL

The life cycle of a cursor in SQL has several steps when processing the row-by-row results of your query. The life cycle of a cursor is defined as:

1. Declaration (DECLARE)

 A cursor is declared by defining a SELECT statement that tells SQL what information your cursor will be processing.

DECLARE cursor_name CURSOR FOR
SELECT column1, column2 FROM table_name;

2. Opening (OPEN)

 When a cursor is opened, the result set is materialised, SQL Server gets the rows ready, and the cursor is positioned before the first row.

OPEN cursor_name;

3. Fetching (FETCH)

 You can fetch each row while fetching data by using the FETCH command. After each FETCH, the cursor moves to the next row.

FETCH NEXT FROM cursor_name INTO @var1, @var2;

4. Processing (Loop)

The processing of each row occurs inside a loop, checked by looking at @@FETCH_STATUS to see if there are more rows to process.

WHILE @@FETCH_STATUS = 0
BEGIN
   --  logic 
   FETCH NEXT FROM cursor_name INTO @var1, @var2;
END

5. Closing (CLOSE)

After the processing, the cursor is closed, which releases the locks but maintains the cursor definition.

CLOSE cursor_name;

6. Deallocation (DEALLOCATE)

Finally, the Deallocate step completely removes the cursor from memory and closes any resources it used.

DEALLOCATE cursor_name;

Syntax and Example for Cursors in SQL

Syntax:

-- To declare variables for storing the column name
DECLARE @col1 DType, @col2 DType, ...
-- Cursor Declaration
DECLARE c_name CURSOR [TYPE] FOR
SELECT col1, col2, ...
FROM table;
OPEN cursor_name;
-- To fetch rows
FETCH NEXT FROM cursor_name INTO @col1, @col2, ...
WHILE @@FETCH_STATUS = 0
BEGIN
    -- logic 
    FETCH NEXT FROM cursor_name INTO @col1, @col2, ...;
END

-- Close the cursor
CLOSE cursor_name;
DEALLOCATE cursor_name;

Parameters involved in syntax:

• Declare @Variable: These are the variables used to store the column values.
• Declare Cursor_name CURSOR: This is used to define a name for a cursor object.
• FETCH NEXT FROM cursor_name INTO @col1: This retrieves the next row and stores it into the declared variables.
• CLOSE cursor_name: To release the result set, it closes the cursor.

Example:

DECLARE @ProID VARCHAR(10), @EmpName VARCHAR(20), @Role VARCHAR(30), @Status VARCHAR(20);
DECLARE project_cursor CURSOR FOR
SELECT Pro_ID, Emp_Name, Role, Status
FROM Pro_det;
OPEN project_cursor;
FETCH NEXT FROM project_cursor INTO @ProID, @EmpName, @Role, @Status;
WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT 'Project ID: ' + @ProID + ', Name: ' + @EmpName + ', Role: ' + @Role + ', Status: ' + @Status;
    FETCH NEXT FROM project_cursor INTO @ProID, @EmpName, @Role, @Status;
END

CLOSE project_cursor;
DEALLOCATE project_cursor;

Output:

Explanation: Here, this query uses a cursor called project_cursor to loop through the Pro_det table and get each project ID with employee name, role, and status, and each row is processed sequentially with a loop until all records have been printed.

Advantages of Cursors in SQL

• Cursors allow a developer to manage results and data manipulation.
• Cursors require little more code than standard SQL, but are easily supported with control-of-flow statements (IF, WHILE, etc.) used with stored procedures.
• Cursors can also be implemented on multiple rows, where each row requires one-at-a-time attention, or you need to call an external procedure for each row.

Disadvantages of Cursors in SQL

• Cursor processing is slower than set-based operations that process the data as a set instead of individual operations, where they are processed one row at a time.
• While the equivalent of a SELECT statement is simply equal to the existing standard SQL SELECT, the syntax to write, manage, and debug a cursor is more complex than standard SQL.
• A cursor holds locks for longer periods, therefore causing blocking and issues with concurrency in environments with multiple users

Alternative Methods for Cursor in SQL 

There are a few alternatives that can be used instead of Cursor, such as using CTEs and WHILE loops in the temporary table.

Using Common Table Expressions (CTE) in SQL

A CTE is a temporary result set that can be referenced in a SELECT, INSERT, UPDATE, or DELETE statement. Recursive CTEs can be useful as an alternative to a cursor when writing hierarchical or iterative queries.

Syntax:

WITH cte_name AS (
    SELECT col1, col2, ..., 1 AS Level
    FROM tab_name
    WHERE condt
    UNION ALL
    SELECT t.col1, t.col2, ..., c.Level + 1
    FROM tab t
    JOIN cte_name c ON t.rel_col = c.matching_column
)

SELECT * FROM cte_name;

Example:

CREATE TABLE Pro_det (
    Pro_ID VARCHAR(10),
    Emp_Name VARCHAR(20),
    Role VARCHAR(30),
    Status VARCHAR(20),
    Reports_To VARCHAR(20) 
);

INSERT INTO Pro_det (Pro_ID, Emp_Name, Role, Status, Reports_To) VALUES
('P001', 'Karan', 'Team Lead', 'Active', NULL),
('P002', 'Isha', 'Developer', 'On Hold', 'Karan'),
('P003', 'Mohit', 'QA Analyst', 'Completed', 'Karan'),
('P004', 'Divya', 'Developer', 'Active', 'Isha'),
('P005', 'Alok', 'Project Manager', 'In Progress', 'Karan');

WITH Pro_Hie AS (
    SELECT Pro_ID, Emp_Name, Role, Status, Reports_To, 1 AS Level
    FROM Pro_det
    WHERE Reports_To IS NULL
    UNION ALL
    SELECT pa.Pro_ID, pa.Emp_Name, pa.Role, pa.Status, pa.Reports_To, ph.Level + 1
    FROM Pro_det pa
    JOIN Pro_Hie ph ON pa.Reports_To = ph.Emp_Name
)

SELECT * FROM Pro_Hie;

Output:

Explanation: Here, this recursive CTE builds a hierarchy by beginning with the top-level Employee, Karan, and continues to join subordinates through the Reports_To relationship again and again.

Using WHILE Loops with Temporary Tables in SQL

Using WHILE loops with ROW_NUMBER() or IDENTITY in a temp table is a cursor alternative that allows you to process one row at a time. It will give you better speed and control than a cursor.

Syntax:

WHILE @CurrentRow <= @MaxRow
BEGIN
 SELECT @SomeID = PrimaryKeyColumn FROM #TempTable WHERE RowID = @CurrentRow;
 -- Perform any operation here using @SomeID
 UPDATE OriginalTable
 SET ColumnName = NewValue
 WHERE PrimaryKeyColumn = @SomeID;
 SET @CurrentRow += 1;
END
DROP TABLE #TempTable;

Example:

CREATE TABLE Task_List (
    Task_ID INT IDENTITY(1,1),
    Task_Name VARCHAR(50),
    Priority INT
);

INSERT INTO Task_List (Task_Name, Priority) VALUES
('Design UI', 2),
('Develop Backend', 1),
('Write Documentation', 3),
('Test Application', 2);

SELECT ROW_NUMBER() OVER (ORDER BY Priority) AS RowID, * 
INTO #TempTasks 
FROM Task_List;
DECLARE @MaxRow INT = (SELECT MAX(RowID) FROM #TempTasks);
DECLARE @CurrentRow INT = 1;
DECLARE @TaskID INT;
WHILE @CurrentRow <= @MaxRow

BEGIN
    SELECT @TaskID = Task_ID FROM #TempTasks WHERE RowID = @CurrentRow;
    UPDATE Task_List
    SET Priority = Priority + 1
    WHERE Task_ID = @TaskID;
    SET @CurrentRow += 1;
END

DROP TABLE #TempTasks;
SELECT * FROM Task_List;

Output:

Explanation: Here, this query increments each task’s priority individually without a cursor in a WHILE loop. It uses a temp table with ROW_NUMBER() to replicate row-wise processing.

Real-world Examples

1. Payroll Report Generation

Retrieve the monthly salary of staff and print them one by one to create individual payslips.

Example:

CREATE TABLE PayRoll_Track (
    Emp_ID INT,
    Emp_Name VARCHAR(50),
    Net_Salary DECIMAL(10,2)
);

INSERT INTO PayRoll_Track VALUES
(101, 'Ravi Nair', 55000.00),
(102, 'Asha Verma', 62000.00),
(103, 'Manish Rao', 48000.00);

DECLARE @EmpID INT, @EmpName VARCHAR(50), @Salary DECIMAL(10,2);
DECLARE salary_cursor CURSOR FOR
SELECT Emp_ID, Emp_Name, Net_Salary FROM PayRoll_Track;
OPEN salary_cursor;
FETCH NEXT FROM salary_cursor INTO @EmpID, @EmpName, @Salary;
WHILE @@FETCH_STATUS = 0

BEGIN
    PRINT 'Payslip: ' + @EmpName + ' | Salary: ' + CAST(@Salary AS VARCHAR);
    FETCH NEXT FROM salary_cursor INTO @EmpID, @EmpName, @Salary;
END

CLOSE salary_cursor;
DEALLOCATE salary_cursor;

Output:

Explanation: Here, this cursor loops through the PayRoll_Track table and, one by one, it returns employee salaries, simulating the process of producing payslips.

2. Updating Orders

The retail store wants to iterate through the orders and update the pending status to processing.

Example:

CREATE TABLE Order_Queue_XP (
    Order_ID INT,
    Item_Name VARCHAR(50),
    Status VARCHAR(20)
);

INSERT INTO Order_Queue_XP VALUES
(1001, 'Wireless Mouse', 'Pending'),
(1002, 'Laptop Stand', 'Pending'),
(1003, 'USB-C Cable', 'Shipped');

DECLARE @OrderID INT;
DECLARE order_cursor CURSOR FOR
SELECT Order_ID FROM Order_Queue_XP WHERE Status = 'Pending';
OPEN order_cursor;
FETCH NEXT FROM order_cursor INTO @OrderID;
WHILE @@FETCH_STATUS = 0

BEGIN
    -- To update the status of the order
    UPDATE Order_Queue_XP
    SET Status = 'Processing'
    WHERE Order_ID = @OrderID;

    -- To display the updated record
    SELECT 'Order Updated:', * 
    FROM Order_Queue_XP 
    WHERE Order_ID = @OrderID;
    FETCH NEXT FROM order_cursor INTO @OrderID;
END

CLOSE order_cursor;
DEALLOCATE order_cursor;

Output:

Explanation: Here, this cursor iterates through all pending orders, updating their status to show that they are now being processed.

Common Mistakes and Best Practices

Common Mistakes

• Failure to Close Cursors: To avoid memory and resource leaks, the cursors have to be properly closed or deallocated.
• Using Cursors Around Set Operations: Cursors are often incorrectly used to carry out actions that could be accomplished using an UPDATE, JOIN, or MERGE.
• Not Using @@FETCH_STATUS Correctly: Not checking the @@FETCH_STATUS can lead to infinite loops or runtime errors if the cursor is at the end.
• Declaring Cursors Without Filtering Data: Declaring a cursor over an entire table is very costly when it doesn’t use a WHERE clause.
• Nested Cursors: Using a cursor within another cursor usually leads to extremely bad performance and should be avoided.

Best Practices

• Whenever it is possible, use set-based operations: SQL is a set-based system, and avoid using cursors unless you have to do row-by-row processing.
• Always CLOSE and DEALLOCATE: Always close and deallocate your cursor resources at the end of usage.
• Keep Cursor Scope Limited and Small: Only retrieve necessary data from the cursor and work with the maximum number of rows to avoid row lock contention and memory overhead.
• Use Read-Only and Forward-Only if you can: If you don’t need to scroll to another row and update it, then use a cursor with READ ONLY and FORWARD_ONLY to increase performance.
• To handle the errors efficiently, add comments: Comment the cursor logic well, and include error handling to prevent silent failures with the rest of your current data processing.

SQL Simplified: Learn for Free, Grow for Life

Master the language of data and start your journey toward a data-centric career—without spending a dime!

Explore Program

Conclusion

SQL Cursors are extremely useful for performing row-by-row operations when set-based processing cannot meet the needs of the data transformation, especially in examining or manipulating procedural aspects of the data. Using cursors can add a great deal of flexibility to your queries, but be careful of the potential performance impact on large data sets. You can use cursors in a variety of instances, such as payroll processing, updating order statuses, etc., which makes it easier to perform very complex logic that cannot easily be expressed in pure SQL. Mastering complete knowledge of SQL cursors will give you an advantage in analysing and working with procedural logic in a relational database system, especially in enterprise applications. 

To learn more about SQL functions, check out this SQL course and also explore SQL Interview Questions prepared by industry experts

SQL Cursors – FAQs