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

The result is:

To fetch the next record write the query as:

Fetch next from rpt3

The result is:

To fetch the previous record write the query as:

Fetch prior from rpt3

The result is:

To fetch the last record write the query as:

Fetch last from rpt3

The result is:

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.