CURSOR in SQL Server

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

-- Syntax

Example for Declaring Cursor

-- Example

DECLARE INFO_CURSOR CURSORFORSELECT*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

--Syntax

OPEN<CURSOR_NAME>

--Example

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

--Syntax

FETCH<CURSOR_NAME>

--Example

FETCH INFO_CURSOR

Example for Closing Cursor

--Syntax

CLOSE<CURSOR_NAME>

--Exaple

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