Home > DeveloperSection > Interviews > How to use cursor in SQL?

Posted on    October-03-2016 3:46 AM

 MSSQL Server SQL Server 2008 
Ratings:
 1 Answer(s)
  142  View(s)
Rate this:

Abhishek Srivasatava
Abhishek Srivasatava

Total Post:70

Points:350
Posted on    October-03-2016 3:46 AM

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 CURSOR
STATIC 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 EESAL
INTO @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 INT
DECLARE EESAL CURSOR
STATIC FOR
SELECT PRSN_INTN_ID, EMPL_STAT_CD ,SALARY FROM EE_SALARY WHERE
    EMPL_STAT_CD='ACTIVE'
 
OPEN EESAL
IF @@cursor_rows > 0
BEGIN
      FETCH NEXT FROM EESAL   
INTO @PRSN_INTN_ID,@EMPL_STAT_CD,@SALARY
WHILE @@fetch_status = 0
      BEGIN
PRINT '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,@SALARY
END
END
 
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.


Modified On Oct-03-2016 03:49:17 AM

Don't want to miss updates? Please click the below button!

Follow MindStick