CURSOR in MSSQL

In SQL Server, a CURSOR is a database object that allows you to traverse and manipulate individual rows returned by a query. It provides a way to process records row by row, which can be useful in certain scenarios when you need to perform operations on a record-by-record basis, such as iterative processing or complex calculations that involve multiple rows.

The syntax for declaring and using a CURSOR in SQL Server is as follows:

DECLARE cursor_name CURSOR
FOR
SELECT column1, column2, ...
FROM your_table
WHERE conditions;

OPEN cursor_name;

FETCH NEXT FROM cursor_name INTO @variable1, @variable2, ...;

WHILE @@FETCH_STATUS = 0
BEGIN
    -- Perform operations on the current row using @variable1, @variable2, ...

    -- Fetch the next row
    FETCH NEXT FROM cursor_name INTO @variable1, @variable2, ...;
END

CLOSE cursor_name;
DEALLOCATE cursor_name;

Now, let's see an example of how to use a CURSOR in SQL Server. Suppose we have a table called "Employee" with columns "EmployeeID," "FirstName," "LastName," and "Salary." We want to give a 10% salary raise to each employee whose salary is below a certain threshold, say $50,000.

Here's how you can achieve this using a CURSOR:

-- Declare variables for cursor
DECLARE @EmployeeID INT;
DECLARE @FirstName NVARCHAR(50);
DECLARE @LastName NVARCHAR(50);
DECLARE @Salary DECIMAL(10, 2);

-- Declare and define the cursor
DECLARE salary_cursor CURSOR
FOR
SELECT EmployeeID, FirstName, LastName, Salary
FROM Employee
WHERE Salary < 50000;

OPEN salary_cursor;

FETCH NEXT FROM salary_cursor INTO @EmployeeID, @FirstName, @LastName, @Salary;

WHILE @@FETCH_STATUS = 0
BEGIN
    -- Perform the salary increase (10% raise)
    SET @Salary = @Salary * 1.1;

    -- Update the new salary in the Employee table
    UPDATE Employee
    SET Salary = @Salary
    WHERE EmployeeID = @EmployeeID;

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

CLOSE salary_cursor;
DEALLOCATE salary_cursor;

In this example, the cursor "salary_cursor" selects all employees with salaries below $50,000. It then goes through each row, calculates a 10% raise, and updates the salary for that specific employee. The WHILE @@FETCH_STATUS = 0 loop ensures that the cursor processes all the eligible rows one by one.

Though cursors can be useful in specific scenarios, it's important to note that they should be used judiciously, as they can be less performant than set-based operations in SQL. In many cases, a well-optimized set-based approach using SQL statements can be more efficient than using a cursor. Cursors should generally be considered as a last resort for complex row-by-row processing.