Monday, September 17, 2012

What is Cursor with examples in SQL Server 2008?

Posted by Rahul Kharde at 10:44 PM


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




If you enjoyed this post and wish to be informed whenever a new post is published, then make sure you subscribe to my regular Email Updates. Subscribe Now!


Kindly Bookmark and Share it:

YOUR ADSENSE CODE GOES HERE

0 comments:

Have any question? Feel Free To Post Below:

 

Popular Posts

Recent Comments

© 2011. All Rights Reserved | Help to understand .Net | Template by Blogger Widgets

Home | About | Top