---
title: "How to use cursor in SQL?"  
description: "How to use cursor in SQL?"  
author: "Abhishek Srivasatava"  
published: 2016-10-03  
updated: 2020-09-20  
canonical: https://www.mindstick.com/interview/23081/how-to-use-cursor-in-sql  
category: "mssql server"  
tags: ["sql server 2008"]  
reading_time: 4 minutes  

---

# How to use cursor in SQL?

**Cursor in SQL**

**Cursor** can be defined as the table’s object which is used to retrieve the data from the result set one row at a time. We use it when there is a need to update records in a table row by row.

## Declare cursor:

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

\

**Example**

```
 DECLARE EESAL CURSORSTATIC FOR SELECT PRSN_INTN_ID, EMPL_STAT_CD ,SALARY FROM
EE_SALARY WHERE    EMPL_STAT_CD='ACTIVE'
```

## Open:

**\**

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

\

**Syntax:**

```
OPEN cursor_name;
```

## Fetch:

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

\

## Example :

```
  FETCH NEXT FROM EESALINTO @PRSN_INTN_ID,@EMPL_STAT_CD,@SALARY
```

## Close:

It is used to close by releasing the current result set and freeing any cursor locks held on the rows on which the cursor is positioned

```
Syntax: CLOSE cursor_name;
```

## De-allocate:

\

\

It is used to removes a cursor reference and released all recourses.

```
Syntax :

DEALLOCATE cursor_name.
```

**How to use Cursor in SQL for mass update?**

Let’s take an example to update the salary for all ACTIVE employee of a company by 1000Rs.

**Step 1:** Creating a table:

```
CREATE TABLE EE_SALARY(PRSN_INTN_ID  int NOT NULL PRIMARY KEY ,EMPL_STAT_CD char(20)  NOT NULL ,SALARY int NOT NULL,);
```

**\**

**Step 2 :** inserting the record:

```
INSERT INTO EE_SALARY (PRSN_INTN_ID, [EMPL_STAT_CD],SALARY)    VALUES (1199667, 'ACTIVE',159978)
```

\

Step 3 :Here, I am explaining about the cursor program with help of small example:\
\

```
DECLARE @PRSN_INTN_ID int, @EMPL_STAT_CD VARCHAR(30),@SALARY INTDECLARE EESAL CURSORSTATIC FOR SELECT PRSN_INTN_ID, EMPL_STAT_CD ,SALARY FROM
EE_SALARY WHERE    EMPL_STAT_CD='ACTIVE' OPEN EESALIF @@cursor_rows >
0BEGIN      FETCH NEXT FROM EESAL    INTO @PRSN_INTN_ID,@EMPL_STAT_CD,@SALARYWHILE @@fetch_status = 0      BEGINPRINT 'PRSN_INTN_ID : '+ convert(varchar(20),@PRSN_INTN_ID)+', EMPL_STAT_CD:' +@EMPL_STAT_CD +',SALARY : '+ convert(varchar(20),@SALARY)UPDATE EE_SALARY SET SALARY=SALARY+1000 WHERE PRSN_INTN_ID=@PRSN_INTN_ID FETCH NEXT FROM EESAL            INTO @PRSN_INTN_ID,@EMPL_STAT_CD,@SALARYENDEND CLOSE EESAL DEALLOCATE EESAL 
```

We are using cursor to update the record row by row which is very useful when we

\

connect SQL to console, form application or web application.

## Answers

### Answer by Abhishek Srivasatava

**Cursor in SQL**

**Cursor** can be defined as the table’s object which is used to retrieve the data from the result set one row at a time. We use it when there is a need to update records in a table row by row.

## Declare cursor:

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

\

**Example**

```
 DECLARE EESAL CURSORSTATIC FOR SELECT PRSN_INTN_ID, EMPL_STAT_CD ,SALARY FROM
EE_SALARY WHERE    EMPL_STAT_CD='ACTIVE'
```

## Open:

**\**

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

\

**Syntax:**

```
OPEN cursor_name;
```

## Fetch:

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

\

## Example :

```
  FETCH NEXT FROM EESALINTO @PRSN_INTN_ID,@EMPL_STAT_CD,@SALARY
```

## Close:

It is used to close by releasing the current result set and freeing any cursor locks held on the rows on which the cursor is positioned

```
Syntax: CLOSE cursor_name;
```

## De-allocate:

\

\

It is used to removes a cursor reference and released all recourses.

```
Syntax :

DEALLOCATE cursor_name.
```

**How to use Cursor in SQL for mass update?**

Let’s take an example to update the salary for all ACTIVE employee of a company by 1000Rs.

**Step 1:** Creating a table:

```
CREATE TABLE EE_SALARY(PRSN_INTN_ID  int NOT NULL PRIMARY KEY ,EMPL_STAT_CD char(20)  NOT NULL ,SALARY int NOT NULL,);
```

**\**

**Step 2 :** inserting the record:

```
INSERT INTO EE_SALARY (PRSN_INTN_ID, [EMPL_STAT_CD],SALARY)    VALUES (1199667, 'ACTIVE',159978)
```

\

Step 3 :Here, I am explaining about the cursor program with help of small example:\
\

```
DECLARE @PRSN_INTN_ID int, @EMPL_STAT_CD VARCHAR(30),@SALARY INTDECLARE EESAL CURSORSTATIC FOR SELECT PRSN_INTN_ID, EMPL_STAT_CD ,SALARY FROM
EE_SALARY WHERE    EMPL_STAT_CD='ACTIVE' OPEN EESALIF @@cursor_rows >
0BEGIN      FETCH NEXT FROM EESAL    INTO @PRSN_INTN_ID,@EMPL_STAT_CD,@SALARYWHILE @@fetch_status = 0      BEGINPRINT 'PRSN_INTN_ID : '+ convert(varchar(20),@PRSN_INTN_ID)+', EMPL_STAT_CD:' +@EMPL_STAT_CD +',SALARY : '+ convert(varchar(20),@SALARY)UPDATE EE_SALARY SET SALARY=SALARY+1000 WHERE PRSN_INTN_ID=@PRSN_INTN_ID FETCH NEXT FROM EESAL            INTO @PRSN_INTN_ID,@EMPL_STAT_CD,@SALARYENDEND CLOSE EESAL DEALLOCATE EESAL 
```

We are using cursor to update the record row by row which is very useful when we

\

connect SQL to console, form application or web application.


---

Original Source: https://www.mindstick.com/interview/23081/how-to-use-cursor-in-sql

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