help regarding cursor

Hi Gokilavasan,

A cursor is implemented by a cursor library. A cursor library is software, often implemented as a part of a database system or a data access API, that is used to manage attributes of data returned from a data source (a result set). These attributes include concurrency management, position in the result set, number of rows returned, and whether you can move forward or backward (or both) through the result set (scrollability).

A cursor keeps track of the position in the result set, and allows you to perform multiple operations row by row against a result set, with or without returning to the original table. In other words, cursors conceptually return a result set based on tables within the databases. The cursor is so named because it indicates the current position in the result set, just as the cursor on a computer screen indicates current position.

Types of Cursors:

1.Forward-only cursor do not support scrolling, they only support fetching rows serially from the start to end.

2.Static cursors are built in tempdb when the cursor is opened. They never reflect changes to the data. Static cursors are always read only, because it is built as a work table in tempdb.

3.Keyset driven cursors have the membership and order of rows in the result set fixed when the cursor is opened.

4.Dynamic cursors are the opposite of static cursors. Dynamic cursors reflect all changes made to the rows in their result set. The data values, order and membership of the rows in the result set can change on each fetch.

Examples

DECLARE @AuthorID char(11)

DECLARE c1 CURSOR READ_ONLY

FOR

SELECT au_id

FROM authors

OPEN c1

FETCH NEXT FROM c1

INTO @AuthorID

WHILE @@FETCH_STATUS = 0

BEGIN

PRINT @AuthorID

FETCH NEXT FROM c1

INTO @AuthorID

END

CLOSE c1

DEALLOCATE c1

Return value Description

The FETCH statement was successful.

-1

The FETCH statement failed or the row was beyond the result set.

-2

The row fetched is missing.

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Senthilkumar14y ago
Accepted answer
Hi Gokilavasan,

A cursor is implemented by a cursor library. A cursor library is software, often implemented as a part of a database system or a data access API, that is used to manage attributes of data returned from a data source (a result set). These attributes include concurrency management, position in the result set, number of rows returned, and whether you can move forward or backward (or both) through the result set (scrollability).

A cursor keeps track of the position in the result set, and allows you to perform multiple operations row by row against a result set, with or without returning to the original table. In other words, cursors conceptually return a result set based on tables within the databases. The cursor is so named because it indicates the current position in the result set, just as the cursor on a computer screen indicates current position.

Types of Cursors:

1.Forward-only cursor do not support scrolling, they only support fetching rows serially from the start to end.
2.Static cursors are built in tempdb when the cursor is opened. They never reflect changes to the data. Static cursors are always read only, because it is built as a work table in tempdb.
3.Keyset driven cursors have the membership and order of rows in the result set fixed when the cursor is opened.
4.Dynamic cursors are the opposite of static cursors. Dynamic cursors reflect all changes made to the rows in their result set. The data values, order and membership of the rows in the result set can change on each fetch.

Examples

DECLARE @AuthorID char(11)
DECLARE c1 CURSOR READ_ONLY
FOR
SELECT au_id
FROM authors

OPEN c1

FETCH NEXT FROM c1
INTO @AuthorID

WHILE @@FETCH_STATUS = 0
BEGIN

PRINT @AuthorID

FETCH NEXT FROM c1
INTO @AuthorID

END

CLOSE c1
DEALLOCATE c1

Return value Description
0
The FETCH statement was successful.
-1
The FETCH statement failed or the row was beyond the result set.
-2
The row fetched is missing.
0
MuthuMari M14y ago
Hi,

Cursor is used for row-by row operations. The cursor is so named because it indicates the current position in the resultset.
A cursor is a set of rows together with a pointer that identifies a current row.

In other word, Cursor is a database object used by applications to manipulate data in a set on a row-by-row basis, its like recordset in the ASP and visual basic.

pls refer this url

http://www.sqlinfo.in/2011/04/sql-server-cursor-simple/

http://www.mssqlcity.com/Articles/General/UseCursor.htm

thanks

If this post is useful then mark it as "Accepted Answer"
0