Scroll Cursor in SQL Server 2008
In this article I describe Scroll cursor. With the help of Scroll Cursor we can scroll first, next, last and any specific row of the table.
Introduction
In this article I describe Scroll cursor. With the help of Scroll Cursor we can scroll first, next, last and any specific row of the table. In this article I also describe how to fetch first, next, last, or any specific row with the help of scroll cursor.
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:
Scroll Cursor:
A Scroll Cursor is a SQL Object, or we can say like a Virtual table that retrieves data from the table one row at a time by scrolling first, next, last, absolute and prior position . We can fetch any record as first, last, prior and specific record from the table.
Declaration of Scroll cursor:
We declare a Scroll Cursor as in the following:
declare scroll_cursor cursor
scroll for
select * from emp
|
Opening the Scroll Cursor:
We open a Scroll Cursor as in the infollowing:
Fetching the first data
fetch first from scroll_cursor
|
Output:
Fetching the next data
fetch next from scroll_cursor
|
Output:
Fetching previous data
fetch prior from scroll_cursor
|
Output:
Fetching Last data
fetch last from scroll_cursor
|
Output:
Fetching 4th data
This fetches the specific data giving absolute position:
fetch absolut 4 from scroll_cursor
|
Output:
Fetching next second data
fetch relative 2 from scroll_cursor
|
Output:
Closing the Scroll Cursor
We close Scroll Cursor as in the following
Deallocating the Scroll Cursor :
We delete Scroll Cursor as following