---
title: "Cursor in Sql server database"  
description: "The Cursor is database object use for getting data and manipulating particular row at a time. It is associated with select query it processes each row"  
author: "Simond Gear"  
published: 2017-02-17  
updated: 2018-03-17  
canonical: https://www.mindstick.com/blog/11294/cursor-in-sql-server-database  
category: "database"  
tags: ["sql server", "sql"]  
reading_time: 2 minutes  

---

# Cursor in Sql server database

The Cursor is [database](https://www.mindstick.com/articles/12226/use-of-database-in-sencha-extjs-and-insert-record-from-user-form-using-ajax) object use for getting data and manipulating particular row at a time. It is associated with [select query](https://www.mindstick.com/articles/1858/sqlite-select-query) it processes each row returned by select query. When we have to [update](https://www.mindstick.com/forum/265/ajax-update-panel) record we use a cursor. With the help of cursor we can verify every row data and modify it or perform calculations which are not possible when we get all [records](https://www.mindstick.com/forum/34640/how-to-create-a-stored-procedure-for-display-all-records) at once.

## It uses following [keyword](https://www.mindstick.com/forum/33572/sql-inner-join-keyword)

**1-**Declare Cursor

**2-**Open

**3-**Fetch

**4-**Close

**5-**Deallocate

**Declare Cursor-**

Syntax for declaring cursor

```
DECLARE cursor_name CURSOR [LOCAL | GLOBAL]  it define cursor scope [FORWARD_ONLY | SCROLL]  --it define cursor movements (forward/backward) [STATIC | KEYSET | DYNAMIC | FAST_FORWARD] --basic type of cursor [READ_ONLY | SCROLL_LOCKS | OPTIMISTIC] --define locks FOR select_statement --define SQL Select statement FOR UPDATE [col1,col2,...coln] --define columns that need to be updated
```

**Open-**

Syntax for opening cursor

**It can be of two type**

**1**-Locally

**2**-Globally

\
**By [default](https://www.mindstick.com/interview/12771/what-is-the-importance-of-default-resources) it is open locally**

```
OPEN [GLOBAL] cursor_name   Open[Local] cursor_Name --it is by defult set
```

## Fetch-

For fetching record we use fetch statement, fetch provide many option for getting record from the cursor.

**Syntax is**

```
FETCH [NEXT|PRIOR|FIRST|LAST|ABSOLUTE n|RELATIVE n]FROM [GLOBAL] cursor_name
```

**Close-**

Close is use for close the cursor

**Syntax for close statement**

CLOSE cursor_name

**Deallocate**

-Deallocate is use for deleting cursor.

**Syntax for Deallocate is**

```
DEALLOCATE cursor_name --After deallocation it can not be reopen
```

## Example

## \

First we [create table](https://www.mindstick.com/articles/443/how-to-create-table-in-sql-server) and name [product](https://www.mindstick.com/articles/75385/full-product-keys)

```
CREATE TABLE Product( ProductID int PRIMARY KEY, Name varchar (50) NOT NULL, Price int NOT NULL, )
```

Then [insert](https://www.mindstick.com/blog/173/executing-insert-delete-or-update-query-in-sqlserver-using-ado-dot-net) some records.

```
INSERT INTO Product(ProductID,Name,Price) VALUES(1,'Ramesh',12000)INSERT INTO Product(ProductID,Name,Price) VALUES(2,'Pavan',25000)INSERT INTO Product(ProductID,Name,Price) VALUES(3,'Suresht',22000)INSERT INTO Product(ProductID,Name,Price) VALUES(4,'Sonu',22000)INSERT INTO Product(ProductID,Name,Price) VALUES(5,'Deepak',28000)
```

## View record

```
 select * from Product
```

![Cursor in Sql server database](https://www.mindstick.com/blogs/d0a21f9c-b9d6-4ce6-a0ba-bdc6a2039054/images/909d0c99-ba5a-43ec-9028-83526cedba8a.png)\

**Complete example of cursor**

```
DECLARE @Id intDECLARE @EmpName varchar(50)DECLARE @ProductID intDECLARE @Price int  DECLARE
cur_emp CURSORSTATIC FOR SELECT ProductID,Name,Price from ProductOPEN cur_empIF @@CURSOR_ROWS
> 0 BEGIN FETCH NEXT FROM cur_emp INTO @ProductID,@EmpName,@Price WHILE @@Fetch_status = 0 BEGIN PRINT 'ProductID : '+ convert(varchar(20),@ProductID)+', EmpName : '+@EmpName+ ', Price : '+convert(varchar(20),@Price) FETCH NEXT FROM cur_emp INTO @ProductID,@EmpName,@Price ENDEND
```

## \

## Output

![Cursor in Sql server database](https://www.mindstick.com/blogs/d0a21f9c-b9d6-4ce6-a0ba-bdc6a2039054/images/96354a83-5213-4ff3-bc7e-b5235f3d5b8b.png)\

## You can also check this related site

[Implementing Concept of Cursor in SQL Server](https://www.mindstick.com/Articles/428/implementing-concept-of-cursor-in-sql-server)

---

Original Source: https://www.mindstick.com/blog/11294/cursor-in-sql-server-database

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