What is Cursor with examples in SQL Server 2008?

What is cursor?

A cursor can be viewed as a pointer to one row in a set of rows. The cursor can only reference one row at a time, but can move to other rows of the result set as needed.

To use cursors in SQL procedures, you need to do the following:

1. Declare a cursor that defines a result set.
2. Open the cursor to establish the result set.
3. Fetch the data into local variables as needed from the cursor, one row at a time.
4. Close the cursor when done

To work with cursors you must use the following SQL statements:

• DECLARE CURSOR
• OPEN CURSOR
• FETCH ROW By ROW
• CLOSE CURSOR

Example

Create the table of Employee

CREATE TABLE Employee

(

EID INT PRIMARY KEY IDENTITY,

ENAME VARCHAR(50),

SALARY DECIMAL(10,2),

DEPT VARCHAR(50)

)

INSERT INTO Employee(ENAME,SALARY,DEPT)VALUES('ABC',2000.00,'HR')

INSERT INTO Employee(ENAME,SALARY,DEPT)VALUES('XYZ',4000.00,'SUPPORT')

INSERT INTO Employee(ENAME,SALARY,DEPT)VALUES('DEF',6000.00,'SUPPORT')

INSERT INTO Employee(ENAME,SALARY,DEPT)VALUES('PQR',1000.00,'HR')

INSERT INTO Employee(ENAME,SALARY,DEPT)VALUES('MNL',7000.00,'MARKETING')

INSERT INTO Employee(ENAME,SALARY,DEPT)VALUES('OPQ',6000.00,'HR')

INSERT INTO Employee(ENAME,SALARY,DEPT)VALUES('RST',9000.00,'ACCOUNT')

SELECT * FROM Employee

Now we are updating salary 20% row by row

DECLARE @EID VARCHAR(50)

DECLARE db_cursor CURSOR FOR

SELECT EID FROM Employee

OPEN db_cursor

FETCH NEXT FROM db_cursor INTO @EID

WHILE @@FETCH_STATUS = 0

BEGIN

UPDATE Employee SET SALARY=SALARY+(SALARY*0.20)

WHERE EID=@EID

FETCH NEXT FROM db_cursor INTO @EID

END

CLOSE db_cursor

DEALLOCATE db_cursor

Result