Cursors in SQL

Cursors in SQL

1. What is a Cursor?

A cursor is a database object used to retrieve, traverse, and manipulate one row at a time from the result set of a query.

Unlike a normal SELECT query that returns all rows at once, a cursor allows you to process rows individually. This is especially useful when you need to perform row-by-row operations (sometimes called procedural processing) that SQL alone cannot do efficiently.

2. When to Use Cursors

Cursors are used when:

  • You need to process each row individually.
  • You cannot achieve your goal with set-based operations.
  • Performing complex calculations or updates row by row.
  • Generating custom reports that require sequential processing.

⚠️ Note: Cursors are usually slower than set-based operations because they process rows one at a time. Only use them when necessary.

3. Cursor Types

Most databases (like SQL Server, Oracle, MySQL) support different types of cursors:

  1. Implicit Cursors
    • Automatically created by SQL when a single-row query is executed.
    • Example: SELECT COUNT(*) FROM Employees;
      You don’t manage the cursor yourself.
  2. Explicit Cursors
    • You define and control them.
    • Used for multi-row queries when you need to fetch rows one at a time.

4. Cursor Lifecycle

A cursor has 4 main steps:

  1. Declare the cursor – Define the SQL query. DECLARE cursor_name CURSOR FOR SELECT column1, column2 FROM table_name WHERE condition;
  2. Open the cursor – Execute the query and make the result set available. OPEN cursor_name;
  3. Fetch rows – Retrieve rows one at a time into variables. FETCH NEXT FROM cursor_name INTO @var1, @var2;
  4. Close and deallocate the cursor – Release memory. CLOSE cursor_name; DEALLOCATE cursor_name;

5. Example in SQL Server

DECLARE @EmployeeID INT, @EmployeeName NVARCHAR(50);

DECLARE EmployeeCursor CURSOR FOR
SELECT EmployeeID, EmployeeName
FROM Employees
WHERE Department = 'Sales';

OPEN EmployeeCursor;

FETCH NEXT FROM EmployeeCursor INTO @EmployeeID, @EmployeeName;

WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT 'Employee ID: ' + CAST(@EmployeeID AS NVARCHAR) + ', Name: ' + @EmployeeName;

    FETCH NEXT FROM EmployeeCursor INTO @EmployeeID, @EmployeeName;
END

CLOSE EmployeeCursor;
DEALLOCATE EmployeeCursor;

Explanation:

  • @@FETCH_STATUS = 0 means the last fetch was successful.
  • Each row is processed individually inside the loop.

6. Pros and Cons

Pros:

  • Can handle complex row-by-row operations.
  • Useful for procedural logic in SQL.

Cons:

  • Slower than set-based operations.
  • Requires careful memory management (open → close → deallocate).

Leave a Reply