---
title: "Cursor in SQL Server"  
description: "In this article, I’m trying to explain the concept of cursor in SQL server."  
author: "Sumit Kesarwani"  
published: 2013-05-13  
updated: 2019-09-07  
canonical: https://www.mindstick.com/articles/1294/cursor-in-sql-server  
category: "database"  
tags: ["database"]  
reading_time: 3 minutes  

---

# Cursor in SQL Server

In this article, I’m trying to [explain the concept](https://www.mindstick.com/forum/158822/explain-the-concept-of-abstract-classes-and-their-significance-in-oop) of [cursor in SQL](https://www.mindstick.com/forum/156784/what-is-cursor-in-sql-database-write-sql-command-to-create-cursor) server.\

Whenever you execute a [select statement](https://www.mindstick.com/forum/160076/explain-the-sql-select-statement-and-how-it-s-used-to-search-for-data-in-a-database), it returns you a set of rows. The entire set is one thing and not a selection of individual rows. This output is useful for the batch-processing [application](https://www.mindstick.com/articles/12824/calculator-application-in-android) but less appealing for [applications](https://www.mindstick.com/articles/12847/how-to-choose-the-right-ethernet-cable-for-industrial-applications) where the user might want to work with rows at a time. The solution to this problem is Cursors. A cursor is a set of rows together with a set of pointers that help in identifying the [current row](https://www.mindstick.com/forum/158089/knockoutjs-comparing-the-current-row-element-with-the-previous-row-element). Cursors are generally used through [stored procedures](https://www.mindstick.com/forum/540/using-stored-procedures-with-entity-framework-in-an-asp-dot-net-application) and triggers. The data retrieved by the cursor is stored in the temdp database.

Working with the cursor is a five step process:

· Declare Cursor

· Open

· Fetch

· Close

· Deallocate

##### Declare Cursor

A cursor must be declared before it is used. The DECLARE CURSOR statement is used to declare the cursor and to set the storage and basic properties of the cursor.

##### Syntax:

DECLARE cursor_name [INSENSITIVE] [SCROLL] CURSOR

FOR SELECT_STATEMENTS

[FOR {READ ONLY | UPDATE [OF column_name] } ]

##### where

· cursor_name : Name of the cursor.

· INSENITIVE : This is to create a [temporary table](https://www.mindstick.com/forum/158395/explain-the-difference-between-a-temporary-table-and-a-regular-table-in-sqlite) just for this cursor.

· SCROLL : It specifies that all the options of the FETCH statement are supported. If SCROLL is Omitted the cursor supports only FETCH NEXT.

· READ ONLY : It prevents any update through the cursor.

· UPDATE : It specifically states that the cursor should allow modification.

· SELECT_STATEMENTS : It is the standard T-SQL statement that is supplying the rows for the cursor.

##### Example

DECLARE Product_Cursor CURSOR

for

Select * from ProductTable

where price>=10000

##### Open Cursor

Open statement is used to open a cursor before it is used.

##### Syntax:

OPEN cursor_name

##### Example

OPEN Product_Cursor

If the cursor was declared with the INSENSITIVE or [STATIC keyword](https://www.mindstick.com/articles/12096/the-static-keyword-the-static-fields) then the OPEN statement creates a temporary table in the tempdb database for holding records.These tables are automatically created by the [SQL Server](https://www.mindstick.com/articles/34/create-table-in-microsoft-sql-server) and they are also deleted by the the Server once the cursor is closed.

##### Fetch

FETCH is used to retrieve the data from the cursor into variables so that you can work with the data.

##### Syntax:

FETCH

[ [ NEXT | PRIOR | FIRST | LAST | ABSOLUTE { n | @n_varaible} | RELATIVE {n | @n_varaible} ]

FROM ]

{ cursor_name }

[ INTO @variable_name1, @variable_name2 … ]

##### Example

```
DECLARE @PRODUCTNAME varchar(50)DECLARE Product_Cursor  CURSORforSelect PRODUCTNAME from ProductTablewhere price>=10000  OPEN Product_Cursor  FETCH NEXT FROM Product_CursorINTO @PRODUCTNAMEPrint @PRODUCTNAMEWHILE @@fetch_status = 0BEGIN       FETCH NEXT FROM Product_Cursor       INTO @PRODUCTNAME       Print @PRODUCTNAMEEND
```

##### Output

![Cursor in SQL Server](https://www.mindstick.com/mindstickarticle/a7d5285a-949a-4846-8819-a6aab8446e52/images/30962e97-9edd-4bdd-bb0f-cbae6a880195.png)

Close Cursor

When the work is over with the cursor then you should execute a CLOSE statement. This statement frees all the rows that are being held by the cursor but it does not destroy the cursor.

##### Syntax:

CLOSE cursor_name

##### Example

CLOSE Product_Cursor

##### Deallocate

DEALLOCATE statement removes the definition of the cursor from the SQL server.

##### Syntax:

DEALLOCATE cursor_name

##### Example

DEALLOCATE Product_Cursor

##### EXAMPLE

```
DECLARE @PRODUCTID int,@PRODUCTNAME varchar(50),@PRICE intDECLARE PRODUCTCURSOR CURSORSTATICFORSELECT PRODUCTID,PRODUCTNAME,PRICE FROM ProductTableOPEN PRODUCTCURSORIF @@cursor_rows > 0BEGIN       FETCH NEXT FROM PRODUCTCURSOR       INTO @PRODUCTID,@PRODUCTNAME,@PRICE       WHILE @@fetch_status = 0       BEGINPRINT 'ID : '+ convert(varchar(20),@PRODUCTID)+', Name :'+@PRODUCTNAME+ ', PRICE : '+convert(varchar(20),@PRICE)            FETCH NEXT FROM PRODUCTCURSOR            INTO @PRODUCTID,@PRODUCTNAME,@PRICE       ENDEND  CLOSE PRODUCTCURSORDEALLOCATE PRODUCTCURSOR
```

##### Output

![Cursor in SQL Server](https://www.mindstick.com/mindstickarticle/a7d5285a-949a-4846-8819-a6aab8446e52/images/4aef49cc-4413-4cca-8771-85350d9ec9c3.png)

---

Original Source: https://www.mindstick.com/articles/1294/cursor-in-sql-server

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