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
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
Views: 26654 | Post Order: 119