SQL Server Cursor Example 

SQL Server cursors are a fundamental tool for database developers who need to process query results row by row. Unlike standard set-based operations in SQL, cursors allow you to manipulate each row individually, making them useful for complex data operations, reporting, and procedural tasks.

What is a SQL Server Cursor?

A SQL Server cursor is a database object used to retrieve, manipulate, and navigate through rows returned by a query. Cursors are particularly helpful when row-by-row processing is required, rather than performing operations on entire sets.

Key Features of SQL Server Cursors

• Allows row-by-row processing of query results
• Supports fetching, updating, and deleting individual rows
• Useful for complex business logic that cannot be achieved with set-based operations
• Can be declared as static, dynamic, forward-only, or keyset-driven

Types of SQL Server Cursors

Understanding the types of cursors is crucial for selecting the right one for your application:

When to Use SQL Server Cursors

Cursors should be used only when necessary. Some common scenarios include:

• Complex row-by-row calculations
• Processing business logic that depends on the previous row’s value
• Generating detailed reports requiring sequential operations
• Data migration or transformation tasks

Basic SQL Server Cursor Example

Let’s start with a simple example that demonstrates the creation, use, and closing of a cursor.

SQL Server Cursor Example – Step-by-Step Guide

SQL Server cursors are a fundamental tool for database developers who need to process query results row by row. Unlike standard set-based operations in SQL, cursors allow you to manipulate each row individually, making them useful for complex data operations, reporting, and procedural tasks.

What is a SQL Server Cursor?

A SQL Server cursor is a database object used to retrieve, manipulate, and navigate through rows returned by a query. Cursors are particularly helpful when row-by-row processing is required, rather than performing operations on entire sets.

Key Features of SQL Server Cursors

• Allows row-by-row processing of query results
• Supports fetching, updating, and deleting individual rows
• Useful for complex business logic that cannot be achieved with set-based operations
• Can be declared as static, dynamic, forward-only, or keyset-driven

Types of SQL Server Cursors

Understanding the types of cursors is crucial for selecting the right one for your application:

When to Use SQL Server Cursors

Cursors should be used only when necessary. Some common scenarios include:

• Complex row-by-row calculations
• Processing business logic that depends on the previous row’s value
• Generating detailed reports requiring sequential operations
• Data migration or transformation tasks

Basic SQL Server Cursor Example

Let’s start with a simple example that demonstrates the creation, use, and closing of a cursor.

📄

-- Step 1: Declare the cursor
DECLARE EmployeeCursor CURSOR FOR
SELECT EmployeeID, FirstName, LastName
FROM Employees;

-- Step 2: Open the cursor
OPEN EmployeeCursor;

-- Step 3: Fetch the first row
DECLARE @EmployeeID INT, @FirstName NVARCHAR(50), @LastName NVARCHAR(50);
FETCH NEXT FROM EmployeeCursor INTO @EmployeeID, @FirstName, @LastName;

-- Step 4: Loop through the result set
WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT 'Employee ID: ' + CAST(@EmployeeID AS NVARCHAR) 
          + ', Name: ' + @FirstName + ' ' + @LastName;

    -- Fetch the next row
    FETCH NEXT FROM EmployeeCursor INTO @EmployeeID, @FirstName, @LastName;
END

-- Step 5: Close and deallocate the cursor
CLOSE EmployeeCursor;
DEALLOCATE EmployeeCursor;

Explanation of the Code

• DECLARE CURSOR: Defines the cursor and the SELECT statement it will process.
• OPEN: Initializes the cursor and populates it with the result set.
• FETCH NEXT: Retrieves the next row from the cursor.
• WHILE loop: Iterates through all rows until there are no more left.
• CLOSE & DEALLOCATE: Releases the resources associated with the cursor.

Advanced SQL Server Cursor Example – Updating Rows

Cursors can also be used for updating records dynamically based on complex logic.

📄

DECLARE UpdateSalaryCursor CURSOR FOR
SELECT EmployeeID, Salary
FROM Employees
WHERE Department = 'Sales';

OPEN UpdateSalaryCursor;

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

FETCH NEXT FROM UpdateSalaryCursor INTO @EmployeeID, @Salary;

WHILE @@FETCH_STATUS = 0
BEGIN
    -- Increase salary by 10% for each employee
    UPDATE Employees
    SET Salary = @Salary * 1.10
    WHERE EmployeeID = @EmployeeID;

    FETCH NEXT FROM UpdateSalaryCursor INTO @EmployeeID, @Salary;
END

CLOSE UpdateSalaryCursor;
DEALLOCATE UpdateSalaryCursor;

Use Case Explanation

• This cursor example updates each employee’s salary in the Sales department by 10%.
• It demonstrates how cursors can perform complex, row-by-row updates where set-based operations might not be sufficient.

SQL Server Cursors

• Not deallocating cursors, leading to memory leaks.
• Using cursors for large datasets unnecessarily, which impacts performance.
• Mixing cursor types without understanding their behavior.

SQL Server cursors are a powerful tool for row-by-row processing, offering flexibility for complex business logic and data operations. While cursors can be resource-intensive, understanding their types, best practices, and real-world applications ensures effective and efficient database management.

📄

-- Step 1: Declare the cursor
DECLARE EmployeeCursor CURSOR FOR
SELECT EmployeeID, FirstName, LastName
FROM Employees;

-- Step 2: Open the cursor
OPEN EmployeeCursor;

-- Step 3: Fetch the first row
DECLARE @EmployeeID INT, @FirstName NVARCHAR(50), @LastName NVARCHAR(50);
FETCH NEXT FROM EmployeeCursor INTO @EmployeeID, @FirstName, @LastName;

-- Step 4: Loop through the result set
WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT 'Employee ID: ' + CAST(@EmployeeID AS NVARCHAR) 
          + ', Name: ' + @FirstName + ' ' + @LastName;

    -- Fetch the next row
    FETCH NEXT FROM EmployeeCursor INTO @EmployeeID, @FirstName, @LastName;
END

-- Step 5: Close and deallocate the cursor
CLOSE EmployeeCursor;
DEALLOCATE EmployeeCursor;

Explanation of the Code

• DECLARE CURSOR: Defines the cursor and the SELECT statement it will process.
• OPEN: Initializes the cursor and populates it with the result set.
• FETCH NEXT: Retrieves the next row from the cursor.
• WHILE loop: Iterates through all rows until there are no more left.
• CLOSE & DEALLOCATE: Releases the resources associated with the cursor.

Advanced SQL Server Cursor Example – Updating Rows

Cursors can also be used for updating records dynamically based on complex logic.

📄

DECLARE UpdateSalaryCursor CURSOR FOR
SELECT EmployeeID, Salary
FROM Employees
WHERE Department = 'Sales';

OPEN UpdateSalaryCursor;

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

FETCH NEXT FROM UpdateSalaryCursor INTO @EmployeeID, @Salary;

WHILE @@FETCH_STATUS = 0
BEGIN
    -- Increase salary by 10% for each employee
    UPDATE Employees
    SET Salary = @Salary * 1.10
    WHERE EmployeeID = @EmployeeID;

    FETCH NEXT FROM UpdateSalaryCursor INTO @EmployeeID, @Salary;
END

CLOSE UpdateSalaryCursor;
DEALLOCATE UpdateSalaryCursor;

Use Case Explanation

• This cursor example updates each employee’s salary in the Sales department by 10%.
• It demonstrates how cursors can perform complex, row-by-row updates where set-based operations might not be sufficient.

SQL Server Cursors

• Not deallocating cursors, leading to memory leaks.
• Using cursors for large datasets unnecessarily, which impacts performance.
• Mixing cursor types without understanding their behavior.

Frequently Asked Questions (FAQs)

1. What is the difference between a cursor and a standard SELECT statement in SQL Server?

A standard SELECT statement processes data in sets, while a cursor processes rows individually, allowing for row-by-row operations, updates, and complex logic that cannot be done in a single set-based operation.

2. Are SQL Server cursors slow?

Cursors can be slower than set-based operations, especially for large datasets, because they process rows individually. Optimizing cursor type and using them judiciously can mitigate performance issues.

3. What are the types of cursors in SQL Server?

SQL Server supports four main cursor types: Forward-Only, Static, Dynamic, and Keyset-Driven. Each has specific behaviors regarding row movement, data visibility, and updates.

4. Can cursors be used to update multiple rows in SQL Server?

Yes, cursors can update multiple rows by fetching each row individually and applying the update logic. However, set-based updates are often more efficient.

5. When should I avoid using SQL Server cursors?

Avoid cursors for large datasets, high-performance requirements, or operations that can be achieved using set-based SQL statements. Use them only when row-by-row processing is necessary.

SQL Server cursors are a powerful tool for row-by-row processing, offering flexibility for complex business logic and data operations. While cursors can be resource-intensive, understanding their types, best practices, and real-world applications ensures effective and efficient database management.