A Cursor is a database object that represents a result set and is used to manipulate data row by row. When a cursor is opened, it is positioned on a row and that row is available for processing.
For example, you can use a single UPDATE statement to update many rows of data. There are times when you want to loop through a series of rows a perform processing for each row. In this case you can use a cursor.
Syntax for Declaring Cursor
DECLARE <CURSOR_NAME> CURSOR FOR SELECT <COLUMN_NAME> FROM <TABLE_NAME> WHERE <CONDITION>
Example for Declaring Cursor
DECLARE INFO_CURSOR CURSOR FOR SELECT * FROM INFO WHERE ID >5
After declaring the cursor we can access the data. Before accessing the data of the cursor it must be opened by using open statement. Directly following a successful opening, the cursor is positioned before the first row in the result set.
Example for Opening Cursor
After opening cursor on a specific row in the result set with the fetch statement. A fetch operation transfers the data of the row into the application. Simply we can say by using fetch statement we read one by one row at a time in forward only by default.
Example for Fetching Cursor
Example for Closing Cursor
Example which demonstrate the use of Scrollable Cursor
This feature not available in SQL Server 2005. This is come from SQL Server 2008 and 2012
declare cur_std scroll cursor for select * from student --Declare a cursor named cur_std
open cur_std -- Open the cursor
fetch next from cur_std -- Fetch next records in the cursor and display it
fetch absolute 3 from cur_std --Fetch 3 records in the cursor
fetch prior from cur_std --Fetch previous record in the cursor