Understanding Cursors in SQL Server: A Comprehensive Guide

Understanding Cursors in SQL Server: A Comprehensive Guide

Cursors in SQL Server are powerful tools used to iterate through a result set row by row, allowing for more precise control over data manipulation. While they offer flexibility, it's crucial to understand their usage, pros, cons, and when to employ them effectively.

How to Use Cursors:

1. Declaring a Cursor:

Cursors are declared using the DECLARE statement, specifying the SELECT query whose result set the cursor will traverse.

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

2. Opening the Cursor:

Once declared, the cursor needs to be opened to begin traversing the result set.

   OPEN cursor_name;        

3. Fetching Rows:

Use the FETCH statement to retrieve rows from the cursor one by one.

   FETCH NEXT FROM cursor_name INTO @variable1, @variable2;        

4. Processing Rows:

Process the fetched rows as required, performing operations or calculations.

5. Closing the Cursor:

After processing, close the cursor to release resources.

      CLOSE cursor_name;        

Example:

Consider a scenario where we want to calculate the total salary of employees in a department:

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

DECLARE SalaryCursor CURSOR FOR

    SELECT EmployeeID, Salary

    FROM Employees

    WHERE DepartmentID = @DepartmentID;

SET @TotalSalary = 0;

OPEN SalaryCursor;

FETCH NEXT FROM SalaryCursor INTO @EmployeeID, @Salary;

WHILE @@FETCH_STATUS = 0

BEGIN

    SET @TotalSalary = @TotalSalary + @Salary;

    FETCH NEXT FROM SalaryCursor INTO @EmployeeID, @Salary;

END

CLOSE SalaryCursor;

DEALLOCATE SalaryCursor;

SELECT @TotalSalary AS TotalSalary;        

Pros and Cons:

Pros:

- Cursors offer granular control over row-level operations.

- Useful for complex business logic requiring row-by-row processing.

- Can navigate through non-contiguous rows.

Cons:

- Performance overhead, especially for large result sets.

- Often slower compared to set-based operations.

- Increased resource consumption and potential for locking/blocking issues.

Benefits:

- Ideal for scenarios where sequential processing or row-level operations are necessary.

- Facilitates complex data manipulations and custom business logic.

Where to Use Cursors:

- When row-level processing is essential.

- For performing custom calculations or data transformations.

- In scenarios where set-based operations aren't feasible or efficient.

Another practical example of using a cursor in SQL:

Let's say you have a table named Employees that stores employee information including their IDs, names, and salaries. You need to perform a salary adjustment for each employee based on their performance review scores stored in another table named PerformanceReviews.

Here's how you can use a cursor to accomplish this task:

-- Declare variables

DECLARE @EmployeeID INT

DECLARE @EmployeeName NVARCHAR(100)

DECLARE @CurrentSalary DECIMAL(10, 2)

DECLARE @PerformanceScore INT

DECLARE @NewSalary DECIMAL(10, 2)

-- Declare cursor

DECLARE employeeCursor CURSOR FOR

SELECT EmployeeID, EmployeeName, Salary

FROM Employees

-- Open cursor

OPEN employeeCursor

-- Fetch first row from cursor

FETCH NEXT FROM employeeCursor INTO @EmployeeID, @EmployeeName, @CurrentSalary

-- Start cursor loop

WHILE @@FETCH_STATUS = 0

BEGIN

    -- Retrieve performance score for the employee

    SELECT @PerformanceScore = PerformanceScore

    FROM PerformanceReviews

    WHERE EmployeeID = @EmployeeID

    

    -- Calculate new salary based on performance

    IF @PerformanceScore > 8

        SET @NewSalary = @CurrentSalary * 1.05  -- 5% increase for outstanding performance

    ELSE

        SET @NewSalary = @CurrentSalary * 1.03  -- 3% increase for satisfactory performance

    

    -- Update employee's salary

    UPDATE Employees

    SET Salary = @NewSalary

    WHERE EmployeeID = @EmployeeID

    

    -- Fetch next row from cursor

    FETCH NEXT FROM employeeCursor INTO @EmployeeID, @EmployeeName, @CurrentSalary

END

-- Close and deallocate cursor

CLOSE employeeCursor

DEALLOCATE employeeCursor        

In this example:

- We declare variables to store employee information and performance-related data.

- We declare a cursor named employeeCursor to select employee records from the Employees table.

- Within the cursor loop, we fetch each employee's information and retrieve their performance score from the PerformanceReviews table.

- Based on the performance score, we calculate a new salary for the employee and update their record in the Employees table.

- Finally, we close and deallocate the cursor.

This example demonstrates how a cursor can be used to perform row-by-row processing, making it useful for scenarios where you need to apply business logic to individual records in a table.