Fetching the Records Through Cursor in Sql Server 2005

Introduction

In this article I explain how to create and use a cursor in SQL, and also how to fetch the records from the table using the cursor. Let's take an example in which I create a database containing the information about Employees, that is the fields of this database are Emp_id, Emp_name, Emp_address, Emp_email, Emp_joiningdate and Emp_contactno.

I want to fetch the First, last, next and previous record of the employees using a cursor. For this use the following steps.

Step 1.  First create the database and table in it containing the Employee details fields.

create table emp_detail(emp_id int,emp_name varchar(20),emp_address varchar(20),emp_email varchar(20),

emp_joiningdate datetime,emp_contactno int)
select * from emp_detail

Step 2. Now Insert some data of employees in your database as.

insert into emp_detail values(1,'Richa','Delhi','[email protected]','2012-07-16 13:54:02.000',987654321)
insert into emp_detail values(2,'Megha','Delhi','[email protected]','2012-07-16 17:13:18.000',6346466)
insert into emp_detail values(3,'Gaurav','Delhi','[email protected]','2012-07-17 08:21:33.000',92412441)
insert into emp_detail values(5,'Soniya','Delhi','[email protected]','2012-07-18 07:37:19.000',883564)
insert into emp_detail values(6,'Veresh','Delhi','[email protected]','2012-07-18 07:43:38.000',7573452)
insert into emp_detail values(7,'Anmol','Delhi','[email protected]','2012-07-18 17:25:00.000',835624)
insert into emp_detail values(9,'Rajiv','Delhi','[email protected]','2012-07-23 15:31:46.000',52345133)

select * from emp_detail

Step 3. Now create a cursor named rpt3 and select all the data of the employee in this, date and time in separate columns; for this write the query as.

Declare rpt3 cursor
scroll for

select emp_id,emp_name,emp_address,emp_email,emp_contactno,convert(varchar,emp_joiningdate,101)as dated,convert(varchar,emp_joiningdate,8)as timing from emp_detail

Step 4. Now to use this cursor, first open it by writing the query as:

open rpt3

After running it we are in the Cursor now.

Step 5. Now to fetch the first record write the query as.

Fetch first from rpt3

Output

To fetch the next record write the query as.

Fetch next from rpt3

Output

To fetch the previous record write the query as.

Fetch prior from rpt3

Output

To fetch the last record write the query as.

Fetch last from rpt3

Output

Summary

In this way we can fetch records using a cursor, after fetching the record you should close the cursor, the same cursor you opened.