Binaryworld - ADO like Cursor operations using T-SQL ... [ SQL Server -> Cursor ]

This article shows how to use SQL cursor to perform MoveFirst, MoveLast, MoveNext, MovePrev and MoveAbsolute operations using T-SQL's SCROLL CURSOR

Click here to copy the following block

USE pubs
GO

Declare @au_lname varchar(100)
Declare @au_fname varchar(100)

-- Execute the SELECT statement alone to show the
-- full result set that is used by the cursor.
SELECT au_lname, au_fname FROM authors
ORDER BY au_lname, au_fname

-- 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

Print '**** Loop through records ****'
FETCH NEXT FROM authors_cursor into @au_lname ,@au_fname
WHILE @@FETCH_STATUS=0
Begin
Print @au_lname + ', ' + @au_fname
FETCH NEXT FROM authors_cursor into @au_lname ,@au_fname
End

-- Fetch the last row in the cursor.
Print '**** Fetch the last row in the cursor ****'
FETCH LAST FROM authors_cursor into @au_lname ,@au_fname
Print @au_lname + ', ' + @au_fname

-- Fetch the row immediately prior to the current row in the cursor.
Print '**** Fetch the row immediately prior to the current row in the cursor. ****'
FETCH PRIOR FROM authors_cursor into @au_lname ,@au_fname
Print @au_lname + ', ' + @au_fname

-- Fetch the second row in the cursor.
Print '**** Fetch the second row in the cursor. ****'
FETCH ABSOLUTE 2 FROM authors_cursor into @au_lname ,@au_fname
Print @au_lname + ', ' + @au_fname

-- Fetch the row that is three rows after the current row.
Print '**** Fetch the row that is three rows after the current row. ****'
FETCH RELATIVE 3 FROM authors_cursor into @au_lname ,@au_fname
Print @au_lname + ', ' + @au_fname

-- Fetch the row that is two rows prior to the current row.
Print '**** Fetch the row that is two rows prior to the current row. ****'
FETCH RELATIVE -2 FROM authors_cursor into @au_lname ,@au_fname
Print @au_lname + ', ' + @au_fname

CLOSE authors_cursor
DEALLOCATE authors_cursor
GO