---
title: "CURSOR in SQL Server"  
description: "In this blog describe the concept of cursor in sql server and how to create cursor in sql server, types of cursor. Here, I’m trying to demonstrate the"  
author: "Anchal Kesharwani"  
published: 2014-08-12  
updated: 2014-09-18  
canonical: https://www.mindstick.com/blog/671/cursor-in-sql-server  
category: "database"  
tags: ["database"]  
reading_time: 2 minutes  

---

# CURSOR in SQL Server

In this blog [describe the concept](https://www.mindstick.com/forum/158622/describe-the-concept-of-a-divide-by-zero-exception-and-how-to-handle-it) of [cursor in sql](https://www.mindstick.com/interview/2594/what-is-cursor-in-sql-server) server and how to create cursor in [sql server](https://www.mindstick.com/articles/12999/what-is-table-valued-function-in-sql-server), types of cursor. Here, I’m trying to demonstrate the good examples of cursors.\

Cursors are [database objects](https://answers.mindstick.com/qa/30925/what-are-the-various-oracle-database-objects) used to traverse the results of an SQL query. Cursor is a database objects to [retrieve data](https://www.mindstick.com/forum/160512/how-to-retrieve-data-from-the-database-in-c-sharp-using-dataadapter) from a result set one row at a time, instead of the sql commands that operate on all the rows in the result set at one time. We use cursor when we need to [update records](https://www.mindstick.com/forum/159026/how-to-update-records-using-update-statements-with-join-in-sql-server) in a [database table](https://www.mindstick.com/forum/159366/how-to-test-regular-expressions-in-sql-without-using-database-table) in singleton fashion means row by row. It is mainly used in [stored procedures](https://www.mindstick.com/forum/540/using-stored-procedures-with-entity-framework-in-an-asp-dot-net-application) and triggers in database. Cursor is like as looping concept. It also used for loop. To use the cursor in sql server it has [life cycle](https://www.mindstick.com/articles/23194/the-life-cycle-of-software-development):

##### Life cycle of cursor

##### Declare

A cursor is declared by defining the SQL statement that returns a result set.

##### Syntax

DECLARE <cursor_name> CURSOR FOR <[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)>

##### Example

```
DECLARE Product_Cursor CURSORforSelect * from PRODUCTwhere ProductPrice>=10000;
```

##### Open

A Cursor is opened and populated by executing the SQL statement defined by the cursor.

##### Syntax

OPEN <cursor_name>

##### Example

OPEN Product_Cursor;

##### Fetch

When cursor is opened, rows can be fetched from the cursor one by one or in a block to do data manipulation.

##### Syntax

```
FETCH[ [ NEXT | PRIOR | FIRST | LAST | ABSOLUTE { n | @n_varaible} | RELATIVE {n | @n_varaible} ]FROM ]{ cursor_name }[ INTO @variable_name1, @variable_name2 … ]
```

##### Example

FETCH Product_Cursor;

##### Close

After data manipulation, we should close the cursor explicitly.

##### Syntax

CLOSE <cursor_name>

##### Example

CLOSE Product_Cursor

##### Deallocate

Finally, we need to delete the cursor definition and released all the system resources associated with the cursor.

##### Syntax

DEALLOCATE <cursor_name>

##### Example

DEALLOCATE Product_Cursor;

##### Example of cursor in sql server

```
Declare @P_ID as intDeclare @P_Name as varchar(50)Declare @P_Price as intDeclare Product_data CURSOR FOR Select ProductID, ProductName ,ProductPrice from  Product  OPEN Product_data    FETCH NEXT FROM Product_data INTO @P_ID, @P_Name, @P_Price        WHILE @@FETCH_STATUS = 0        BEGIN                     PRINT 'Product ID : '+ convert(varchar(20),@P_ID)+', Product Name :'+convert(varchar(50),@P_Name)+ ', Product PRICE : '+convert(varchar(20),@P_Price)            FETCH NEXT FROM Product_data            INTO @P_ID, @P_Name, @P_Price                    END    CLOSE Product_dataDEALLOCATE Product_data;
```

\
![CURSOR in SQL Server](https://www.mindstick.com/blogs/b38f40b0-1a14-439c-a7f8-b0034aa7025c/images/bddcbee9-2d73-4e6d-90e7-9c7e46441483.png)

---

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

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