Keyset-driven Cursor in SQL Server 2008

In this article I describe Keyset-driven cursor in SQL server 2008.
  • 4115

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:

cursor-in-sql.jpg

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:

open key_cursor

Fetching first data from Keyset-drivin Cursor:

fetch firstfrom key_cursor

Outout:

keyDriven-cursor-in-sql-server-first1.jpg

Closing the Keyset-drivin Cursor:

We close Keyset-drivin Cursor as in the following

close key_cursor

Dealloting Keyset-drivin Cursor:

We deallocate Keyset-drivin Cursor as following

deallocate key_cursor

© 2020 DotNetHeaven. All rights reserved.