What is Cursor in SQL?

In SQL, 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. SQL 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 SQL 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

Why and When to Use a Cursor?

1. Row-by-Row Processing Needed

When you need to process each row individually, like reading, updating, or validating data, one row at a time.
Useful in cases where logic can’t be written using a single SQL query.

2. Complex Business Logic Per Row

If each row requires custom logic, such as calling a stored procedure or running conditional checks before updating.

3. Order-Sensitive Operations

When the sequence of data matters (e.g., calculating running totals or time-based operations), cursors allow control over row order.

4. Row-Dependent Calculations

If processing a row depends on the result of the previous row (e.g., balance carried forward), set-based logic fails, and a cursor is useful.

5. Performing Operations Not Supported in Set-Based SQL

When logic involves procedural steps like loops, conditions, and iteration, SQL is not designed to handle bulk operations.

6. Interacting with External Systems Per Row

Sometimes, each row’s data must trigger an action like sending an email, logging to a file, or interacting with another application.

7. Step-by-Step Debugging and Auditing

Cursors are easier to debug when you want to observe how each row is processed, especially in a development or testing phase.

8. Migrating Data with Transformation Logic

Useful during ETL (Extract, Transform, Load) tasks where data from one source needs transformation and row-level mapping before loading.

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

Cursor type	Control	Use case	Efficiency
Implicit Cursor	No control by the user	It is simple and single-row queries	It is efficient for simple tasks
Explicit Cursor	Full control by the user	It can be used when row-by-row operations are required	It is less efficient and resource-intensive
Static Cursor	Controlled by the user	Used when the data does not change dynamically	It is more resource-intensive, but generally faster than dynamic cursors
Dynamic Cursor	It is also controlled by the user, but it is automatically updated with the data changes	Used when we need to update continuously, such as real-time data	Due to the real-time tracking overhead increase, which reduces the performance
Forward-only Cursor	Limited control by the user, such as forward control	This can be used when we need to iterate over data in one direction	Generally faster than dynamic cursors

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.
SQL 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 Cursor in SQL?

To create 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;

Get 100% Hike!

Master Most in Demand Skills Now!

Syntax and Example for Cursors in SQL

Below are the syntax and examples for SQL cursors and how to implement them in a database.

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 and Drawbacks of Cursors in SQL

There are some drawbacks of using cursors in SQL that are mentioned below:

Adds complexity to SQL logic
Use only when row-by-row logic is truly required.
Slow performance on large datasets
Consumes more memory
Not optimized for set-based processing

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 CTE in SQL 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:

Using Common Table Expressions (CTE) in SQL

Explanation: Here, this Recursive CTE in SQL 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:

Using WHILE Loops with Temporary Tables in SQL

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.

SQL Cursor vs Set-Based Operations: Which One to Choose?

Aspect	SQL Cursor	Set-Based Operation
Processing Style	Row-by-row (procedural logic)	Entire set at once (declarative logic)
Performance	Slower, high memory and CPU usage	Faster, optimized by SQL engine
Use Case	When each row needs different logic	When the same logic applies to all rows
Complex Logic Handling	Good for complex, conditional, or sequential logic	Limited to what can be expressed in SQL
Code Simplicity	More code and harder to maintain	Cleaner and easier to understand
Best For	Row-dependent tasks, external interactions, auditing	Filtering, aggregating, updating large datasets
Example Scenario	Calculate bonus only for employees meeting different criteria per row	Give a 10% raise to all employees in the Sales department
Recommended Use	Only when set-based solutions are not possible	Preferred choice in most situations

Note: Use set-based operations for performance and simplicity; they’re optimized by SQL engines. Choose SQL cursors only when row-level logic is required and can’t be handled using standard queries. Always consider performance impact when deciding.

Real-World Use Cases of SQL Cursors

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:

Payroll Report Generation in SQL Cursors

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 SQL cursors have to be properly closed or deallocated.
Using Cursors Around Set Operations: SQL 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 SQL 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 SQL 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

Q1. How to use cursor in SQL?

Declare the cursor, open it, fetch rows one by one, then close and deallocate it to process query results sequentially.

Q2. When do you need to use cursor in SQL?

When you need to process records in a row-by-row manner, especially when logic can not be formulated.

Q3. Will the cursor decrease the performance?

Yes, cursors are slower than set-based queries. Use cursors when you have to, and set the cursor to READ ONLY and FORWARD_ONLY for optimal settings.

Q4. What would happen with my cursor if I did not close or deallocate it?

There is a chance of a memory leak, lock, or running out of the number of open cursors the database engine allows.

Q5. Can I update data with cursor?

Yes, you can declare cursor for UPDATE, and then populate target rows one at a time.

Q6. Are cursors supported in all databases?

Cursors are supported by almost all relational databases, such as SQL Server, Oracle, MySQL, PostgreSQL, etc. The syntax and behaviour may differ.

Q7. What is Cursor vs While Loop in SQL?

Cursor vs while loop in SQL is that a cursor explicitly fetches rows one at a time from a result set, while a WHILE loop uses manual control logic to iterate through data. Cursors are easier for row-by-row operations, but are generally slower and more resource-intensive than WHILE loops.