---
title: "Explain the SQL CURSOR with example."  
description: "A cursor is a database object that enables traversal over the records in a result set. It allows sequential access to individual rows returned by a SQ"  
author: "Ashutosh Patel"  
published: 2024-07-15  
updated: 2024-07-15  
canonical: https://www.mindstick.com/blog/304490/explain-the-sql-cursor-with-example  
category: "mssql server"  
tags: ["mssql server", "sql server", "sql"]  
reading_time: 3 minutes  

---

# Explain the SQL CURSOR with example.

#### SQL Server Cursor

A cursor is a database object that allows hovering over records in a result set. It allows [sequential](https://www.mindstick.com/interview/23447/what-are-the-different-methods-for-sequential-supervised-learning) access to the individual rows returned by the [SQL query](https://www.mindstick.com/forum/529/rename-table-name-and-column-name-using-sql-query). Cursors are especially useful when you need to work with data [line by line](https://www.mindstick.com/interview/34101/how-do-you-read-a-file-line-by-line-using-a-generator-like-approach-e-g-yield-return), especially in scenarios where you want to perform [operations](https://www.mindstick.com/blog/304985/how-does-devops-bridge-the-gap-between-development-and-operations-teams-like-git) 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.

```plaintext
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

```plaintext
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](https://www.mindstick.com/articles/44463/business-to-business-vat-reclaiming-a-guide-for-your-employees) table.

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

**[Fetch Data](https://www.mindstick.com/forum/160510/how-to-fetch-data-from-the-database-in-the-dot-net-console-application-using-c-sharp) into Variables and Process**

```plaintext
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](https://answers.mindstick.com/qa/115128/what-daily-habits-most-effectively-boost-immune-system-function-and-resilience) returns the status of the last cursor fetch operation, where `0` indicates success and `-1` indicates no more rows.

## Close and Deallocate the Cursor

```plaintext
CLOSE emp_cursor;
DEALLOCATE emp_cursor;
```

Once the [entire row](https://answers.mindstick.com/qa/46828/if-i-buy-an-entire-row-of-seats-on-a-long-flight-so-no-one-is-next-to-me-can-the-airline-sell-one-of-my-empty-seats-to-someone-or-will-they-respect-the-fact-that-the-empty-seats-are-mine-to-do-as-i-p) 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](https://www.mindstick.com/blog/304484/explain-the-sql-stored-procedures) and use cursor into it,

```plaintext
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-

![Explain the SQL CURSOR with example.](https://www.mindstick.com/blogs/826dca1d-1e51-48ee-9a17-0a5a1904bba3/images/cd6d1d1a-4433-401c-9911-5e636710a9be.png)

**Also, Read:** [Explain the SQL triggers and their uses](https://www.mindstick.com/articles/336344/explain-the-sql-triggers-and-their-uses)

---

Original Source: https://www.mindstick.com/blog/304490/explain-the-sql-cursor-with-example

Copyright © MindStick Software Pvt. Ltd. This Markdown version is provided for developers, AI systems, and offline reading.