Presents your 
SQL SERVER E-NEWSLETTER for April 17, 2003

<------------------------------------------->

USE FETCH RELATIVE TO FINE-TUNE YOUR SCROLL CURSORS

FETCH allows you to create SCROLL cursors that pull data from specific
rows by using arguments that determine exactly where you want to start
your cursor. These arguments include FIRST, LAST, PRIOR, and NEXT.

If you want to be even more exact, you can use the RELATIVE argument to
return rows in relation to the cursor position:

RELATIVE {n}

A positive integer returns rows after the current cursor position, and 
a negative integer returns rows prior to the cursor position.

The following example creates an authors_cursor from the pubs database
that pulls data from the third row after the cursor position:

USE pubs
GO

-- Declare the cursor.
DECLARE authors_cursor SCROLL CURSOR FOR
SELECT au_lname, au_fname FROM authors
ORDER BY au_lname, au_fname

OPEN authors_cursor

-- Fetch the row that is three rows after tthe current row.
FETCH RELATIVE 3 FROM authors_cursor

CLOSE authors_cursor
DEALLOCATE authors_cursor
GO

You also can use FETCH RELATIVE to search rows prior to the cursor
position. In the example below, we will first position the cursor on 
the last row, and then fetch data from the row located two rows away:

USE pubs
GO

-- Declare the cursor.
DECLARE authors_cursor SCROLL CURSOR FOR
SELECT au_lname, au_fname FROM authors
ORDER BY au_lname, au_fname

OPEN authors_cursor

-- Fetch the row at the end of the table.
FETCH LAST FROM authors_cursor

-- Fetch the row that is two rows prior to  the current row.
FETCH RELATIVE -2 FROM authors_cursor

CLOSE authors_cursor
DEALLOCATE authors_cursor
GO

----------------------------------------