---

Search posts under SQL Server:

---

Scroll cursor example in SQL Server

How to create a scroll cursor in SQL Server?

In previous post, we learnt about forward only Cursor in SQL Server.

In this post, we shall learn scroll cursor in SQL Server that let us move forward, backward, go to specific row in the result set.

Scroll cursor comes with following fetch options

• FIRST - fetches first row/record from the cursor result set
• LAST - fetches last row from the cursor result set
• PRIOR - fetches previous row from the current cursor position
• NEXT - fetches next row from the current cursor position
• RELATIVE n - fetches n^th row from current cursor position
• ABSOLUTE n - fetches n^th row from the first cursor position

Here is how we can declare a scroll cursor.

DECLARE myCursor SCROLL CURSOR FOR
    SELECT AutoId, FirstName, LastName FROM PersonalDetails

OPEN myCursor
-- 1st row
FETCH NEXT FROM myCursor

-- last row
--FETCH LAST FROM myCursor

-- 2nd row from top
FETCH ABSOLUTE 2 FROM myCursor

-- 3rd row after current row
FETCH RELATIVE 2 FROM myCursor

-- 1st row
FETCH FIRST FROM myCursor

-- next row from current position
FETCH NEXT FROM myCursor
-- previous row from current position
FETCH PRIOR FROM myCursor

CLOSE myCursor
DEALLOCATE myCursor

In case, we have already reached to the last row of the cursor result set and again called FETCH options like FIRST, RELATIVE, ABSOLUTE etc., it goes back to the first row again and count the rows to give result.

Output