Scroll Cursor in SQL Server :-
Example :-
Declare c1 cursor scroll for select statement
By default cursors supports forward only scrolling and supports fetch next statement.
If cursor is declared with scroll option it can fetch both forward & backward scrolling, and also supports all fetch statement like fetch next prior, first,last, absolute n, relative n.
Absolute n:- Means starting from first record fetch the ‘n’th record.
Example :-
1
2
3 absolute 4
4 Ans: 5th record.
| 5 |
Relative n :- means fetches the ‘n’ th record starting from current record.
Example :-
Declare c1 cursor scroll
For select ename from emp
Declare @ename varchar(20)
Open c1
Fetch first from c1 into @ename
Print @ename
Fetch next from c1 into @ename
Print @ename.
Fetch absolute 5 from c1 into @ename
Print @ename
Fetch relative 5 from c1 into @ename
Print @ename
Fetch last from c1 into @ename
Print @ename
Fetch prior from c1 into @ename
Print @ename
Close c1
Deallocate c1.
Write a program to display every 4th record in emp table?
Declare c1 cursor scroll
For select ename from emp
Declare @ename varchar (20)
Open c1
Fetch first from c1 into @ename
While (@@ fetch_status==0)
Begin
Print @ename
Fetch relative 3 from c1 into @ename
End
Close c1
Deallocate c1.