help regarding cursor

Question

what is mean by cursor in sql server.explain with a simple example.

Senthilkumar · 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.

MuthuMari M · Answer

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 "

help regarding cursor

Answers (2)