SQL Server Basics of Cursors

CURSOR :

Cursor is a pointer to the temporary memory where data is retrieved from a table is stored. Main purpose of cursor is to provide access to one by one row in sequence from a table.

To work with cursor you have to perform the following five steps:

1. Cursor Declaration :

The first step to work with cursor is declaring the cursor. In cursor declaration you have to provide a name to the cursor, type of cursor and associate a 'select' statement to the cursor.

syntax: 

declare <cursor name> Cursor

[local/global]

[forwared-only/scroll]

[static/dynamic/keyset]

for [select statement]

2. Opening Cursor:

When we open the cursor then the select statement associated with cursor will be executed, rows retrieved by SELECT will be stored in temporary memory & CURSOR will point to that memory.

syntax:

OPEN <cursor name>

3. Fetching Data from Cursor :

When you have to make any changes to the data available in Cursor, you must copy the data from Cursor into variables in the program & this process of copying data from cursor into variables in the program is called Fetching Data.

Syntax: 

fetch  nexy/prior/first/last/relative/

absolute<n> from <cursor name> into

<local variable list>

After fetching a row to verify whether or not the fetch statement successfully fetches a row, use system variable "@@FETCH_STATUS". This variable will contain the value zero when 'fetch' was Success.

0 - fetch was success

1 - if the row try to fetch was beyond cursor.

2 - if the row try to fetch was missing in cursor.

4. Closing Cursor

After accessing data from cursor is completed you can close the cursor, when you close then link to temporary memory from your program will be closed, but the memory allocated for the cursor will not de-allocated. Hence, you can reopen the cursor.

syntax: Close <cursor name>

5. De-allocating Cursor

You can de-allocate the complete memory allocated for the cursor & once the cursor is de-allocated you can reopen the cursor without executing declaration statement again.

Syntax:

Deallocate <cursor name>

Simple Example for Cursor :

 CREATE TABLE Employee

(

 EmpID int PRIMARY KEY,

 EmpName varchar (50) NOT NULL,

 Salary int NOT NULL,

 Address varchar (200) NOT NULL,

)

GO

INSERT INTO Employee(EmpID,EmpName,Salary,Address) VALUES(1,'Mohan',12000,'Noida')

INSERT INTO Employee(EmpID,EmpName,Salary,Address) VALUES(2,'Pavan',25000,'Delhi')

INSERT INTO Employee(EmpID,EmpName,Salary,Address) VALUES(3,'Amit',22000,'Dehradun')

INSERT INTO Employee(EmpID,EmpName,Salary,Address) VALUES(4,'Sonu',22000,'Noida')

INSERT INTO Employee(EmpID,EmpName,Salary,Address) VALUES(5,'Deepak',28000,'Gurgaon')

GO

SELECT * FROM Employee 

SET NOCOUNT ON

DECLARE @Id int

DECLARE @name varchar(50)

DECLARE @salary int

DECLARE cur_emp CURSOR

STATIC FOR 

SELECT EmpID,EmpName,Salary from Employee

OPEN cur_emp

IF @@CURSOR_ROWS > 0

BEGIN 

FETCH NEXT FROM cur_emp INTO @Id,@name,@salary

WHILE @@Fetch_status = 0

BEGIN
PRINT 'ID : '+ convert(varchar(20),@Id)+', Name : '+@name+ ', Salary : '+convert(varchar(20),@salary)

FETCH NEXT FROM cur_emp INTO @Id,@name,@salary

END

END

CLOSE cur_emp

DEALLOCATE cur_emp

SET NOCOUNT OFF 

Summary:

What do you think about this Article?

Please give feedback to dotnetcircle@gmail.com

For any queries, ideas or any other mail to dotnetcircle@gmail.com