Keyset-driven Cursor in SQL Server 2008
In this article I describe Keyset-driven cursor in SQL server 2008.
Introduction
In this article I describe keyset-driven cursor. With the help of keyset-drivin cursor we can find first, next, last and any specific row of the table.
First we create a table named emp to apply a cursor on this table:
Creation of table
create table emp(emp_id int,em_name varchar(10))
go
insert into emp
select 1,'d' union all
select 2,'e' union all
select 3,'f' union all
select 4,'mahi' union all
select 5,'gill' union all
select 6,'singh'
go
select * from emp
|
Output:
Keyset-driven cursor:
It lies between a static and a dynamic cursor. When we open keyset-drivin cursor than it saves the list of unique values for the entire result set, these values are called keyset. These keys retrieve the current data values for each row.
Creation of Keyset-drivin cursor:
declare key_cursor cursor
keyset for
select * from emp
|
Opening of Keyset-drivin cursor:
We open a Keyset-drivin Cursor as in the in following:
Fetching first data from Keyset-drivin Cursor:
fetch firstfrom key_cursor
|
Outout:
Closing the Keyset-drivin Cursor:
We close Keyset-drivin Cursor as in the following
Dealloting Keyset-drivin Cursor:
We deallocate Keyset-drivin Cursor as following