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.
  • 3627

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:

cursor-in-sql.jpg

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:

open scroll_cursor

Fetching the first data

fetch first from scroll_cursor

Output:

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

Fetching the next data

fetch next from scroll_cursor

Output:

scroll-cursor-in-sql-serever-nest2.jpg

Fetching previous data

fetch prior from scroll_cursor

Output:

scroll-cursor-in-sql-serever-priveos3.jpg

Fetching Last data

fetch last from scroll_cursor

Output:

scroll-cursor-in-sql-serever-last4.jpg

Fetching 4th data

This fetches the specific data giving absolute position:

fetch absolut 4 from scroll_cursor

Output:

scroll-cursor-in-sql-serever-absolute5.jpg

Fetching next second data

fetch relative 2 from scroll_cursor

Output:

scroll-cursor-in-sql-serever-relative6.jpg

Closing the Scroll Cursor

We close Scroll Cursor as in the following

close scroll_cursor

Deallocating the Scroll Cursor :

We delete Scroll Cursor as following

deallocate scroll_cursor

© 2019 DotNetHeaven. All rights reserved.