SQL Server Cursor

A cursor is a database object that allows hovering over records in a result set. It allows sequential access to the individual rows returned by the SQL query. Cursors are especially useful when you need to work with data line by line, especially in scenarios where you want to perform operations involving complex logic or where set-based operations (typical SQL operations) are not sufficient.

Creating SQL Cursor

Suppose we have a table named Employees with the following settings.

USE MyTestDB
GO
CREATE TABLE Employees (
   EmployeeID INT PRIMARY KEY NOT NULL,
   FirstName VARCHAR(50) NOT NULL,
   LastName VARCHAR(50),
   Position VARCHAR(50),
   Salary DECIMAL(10, 2) NOT NULL
);

Let’s assume that we want to create a stored procedure that calculates and publishes the annual salary for each employee in the Employees table.

Here's how you can use the cursor to do this,

Declare the Cursor

DECLARE emp_cursor CURSOR FOR
SELECT EmployeeID, FirstName, LastName, Salary
FROM Employees;

Declares a cursor named emp_cursor that selects the EmployeeID, FirstName, LastName, and Salary from the Employees table.

Open the Cursor
OPEN emp_cursor;
This opens the cursor, ready to fetch the rows.

Fetch Data into Variables and Process

DECLARE @EmpID INT;
DECLARE @FirstName VARCHAR(50);
DECLARE @LastName VARCHAR(50);
DECLARE @Salary DECIMAL(10, 2);
FETCH NEXT FROM emp_cursor INTO @EmpID, @FirstName, @LastName, @Salary;
WHILE @@FETCH_STATUS = 0
BEGIN
   -- Perform operations on each row
   DECLARE @AnnualSalary DECIMAL(10, 2);
   SET @AnnualSalary = @Salary * 12;
   
   -- Print or use the data as needed
   PRINT 'Employee: ' + @FirstName + ' ' + @LastName + ', Annual Salary: ' + CAST(@AnnualSalary AS VARCHAR);
   
   -- Fetch the next row
   FETCH NEXT FROM emp_cursor INTO @EmpID, @FirstName, @LastName, @Salary;
END;

The FETCH NEXT statement retrieves the next declared variables from the cursor. The @@FETCH_STATUS system function returns the status of the last cursor fetch operation, where 0 indicates success and -1 indicates no more rows.

Close and Deallocate the Cursor

CLOSE emp_cursor;
DEALLOCATE emp_cursor;

Once the entire row is processed, you must close the cursor to release the sticky objects.

In this example, the cursor emp_cursor traverses each row in the Employees table, calculates an annual salary based on monthly salary (Salary * 12), and prints the result for each employee

Example- 

Create a SQL Stored Procedure and use cursor into it,

USE MyTestDB
GO
CREATE PROCEDURE Proc_EmpCursor
AS
BEGIN
SET NOCOUNT ON;
-- Declare cursor
DECLARE emp_cursor CURSOR FOR
SELECT EmployeeID, FirstName, LastName, Salary
FROM Employees;
-- open cursor
OPEN emp_cursor;
-- fetch data into variable for process
DECLARE @EmpID INT;
DECLARE @FirstName VARCHAR(50);
DECLARE @LastName VARCHAR(50);
DECLARE @Salary DECIMAL(10, 2);
FETCH NEXT FROM emp_cursor INTO @EmpID, @FirstName, @LastName, @Salary;
WHILE @@FETCH_STATUS = 0
BEGIN
 -- Perform operations on each row
 DECLARE @AnnualSalary DECIMAL(10, 2);
 SET @AnnualSalary = @Salary * 12;
   
 -- Print or use the data as needed
 PRINT 'Employee: ' + @FirstName + ' ' + @LastName + ', Annual Salary: ' + CAST(@AnnualSalary AS VARCHAR);
   
 -- Fetch the next row
 FETCH NEXT FROM emp_cursor INTO @EmpID, @FirstName, @LastName, @Salary;
END;
-- close and deallocate cursor
CLOSE emp_cursor;
DEALLOCATE emp_cursor;

SET NOCOUNT OFF;
END

Execure-

Also, Read: Explain the SQL triggers and their uses