SQL Server Cursors Explained with Example

🧩 Introduction

In SQL Server, sometimes an application requires processing data one row at a time, rather than as a complete set.
To handle such situations, SQL Server provides a special database object called a Cursor.

A Cursor allows you to retrieve a set of rows and then manipulate each row individually, giving precise control over data operations that cannot be easily done using set-based SQL statements.

⚙️ Sections of a SQL Server Cursor

A cursor typically consists of seven key sections, each with a specific purpose:

1. Declaration of Cursor – Define the cursor and its result set.
2. SQL Query for Fetching Records – The SELECT statement that determines which rows to process.
3. Open Cursor – Opens the cursor and allocates memory to hold the rows.
4. Fetch Statement – Retrieves one row at a time from the result set into local variables.
5. @@FETCH_STATUS – A system variable that indicates the result of the last fetch:
   • 0 → Fetch successful
   • -1 → Fetch failed or no more rows
   • -2 → Row fetched is missing
6. BEGIN…END Block – Contains the SQL logic that processes each fetched row.
7. Close and Deallocate – Closes the cursor and releases allocated memory.

🧠 Syntax for Cursor in SQL Server

DECLARE @Cursor_Name CURSOR FOR
    SELECT column1, column2 FROM TableName WHERE <conditions>

OPEN @Cursor_Name

FETCH NEXT FROM @Cursor_Name INTO @Variable1, @Variable2

WHILE @@FETCH_STATUS = 0
BEGIN
    -- SQL statements to process the current row
    FETCH NEXT FROM @Cursor_Name INTO @Variable1, @Variable2
END

CLOSE @Cursor_Name
DEALLOCATE @Cursor_Name

💡 Example: Updating Customer Amounts with Cursor

Let’s assume we have two tables:

CREATE TABLE Customer (
  CustomerID INT IDENTITY(1,1) PRIMARY KEY,
  CustNumber NVARCHAR(100),
  CustFName NVARCHAR(100),
  CustLName NVARCHAR(100),
  Email NVARCHAR(100),
  Amount INT,
  RegDate DATETIME
);

CREATE TABLE CustomerTransaction (
  TransactionID INT IDENTITY(1,1) PRIMARY KEY,
  CustID INT,
  TransactionAmt INT,
  Mode NVARCHAR(10),
  TranDate DATETIME
);

🧾 Requirement:

Increase each customer’s amount by 20%, and record this transaction in the CustomerTransaction table.

✅ Stored Procedure Using Cursor

CREATE PROCEDURE UpdateCustAmount
AS
BEGIN
    DECLARE @CustID INT;
    DECLARE @Amount INT;
    DECLARE @AddAmt INT;

    DECLARE Cur_Cust CURSOR FOR
        SELECT CustomerID, Amount
        FROM Customer;

    OPEN Cur_Cust;

    FETCH NEXT FROM Cur_Cust INTO @CustID, @Amount;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @AddAmt = (20 * @Amount) / 100;

        UPDATE Customer
        SET Amount = Amount + @AddAmt
        WHERE CustomerID = @CustID;

        INSERT INTO CustomerTransaction (CustID, TransactionAmt, Mode, TranDate)
        VALUES (@CustID, @AddAmt, 'C', GETDATE());

        FETCH NEXT FROM Cur_Cust INTO @CustID, @Amount;
    END;

    CLOSE Cur_Cust;
    DEALLOCATE Cur_Cust;
END;

🔍 Summary

Cursors in SQL Server are powerful but should be used carefully.

✅ Advantages:

• Simplifies complex row-by-row operations.
• Offers procedural control within SQL.
• Useful for administrative or serialized tasks.

⚠️ Disadvantages:

• Can decrease performance when working with large result sets.
• Requires more memory and CPU for row-by-row processing.

💡 Pro Tip:

Whenever possible, replace cursors with set-based queries, WHILE loops, or CASE statements for better performance.
However, cursors remain valuable when the logic must process rows sequentially or depends on the output of previous rows.

🏁 Conclusion

SQL Server cursors are a vital part of T-SQL for situations that demand fine-grained, row-level control.
Understanding how to declare, open, fetch, close, and deallocate cursors effectively will help you design more flexible and reliable stored procedures.