Home > DeveloperSection > Beginner > Implementing Concept of Cursor in SQL Server

Implementing Concept of Cursor in SQL Server

Database Database 
0 Comment(s)
 5507  View(s)
Rate this:

Implementing Concept of 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.

Some system stored procedure related with cursor

1)      sp_cursor_listà returns a list of cursor currently visible on the connection and their attribute

2)      sp_describe_cursoràDescribe the attributes of a cursor such as whether it is forward only or scrolling cursor.

3)      sp_describe_cursor_columnsàDescribe the attributes of the columns in the cursor result set.

4)      sp_describe_cursor_tablesàDescribe the base tables accessed by the cursor.

Steps for implementing a simple cursor in your program

1)      A programmer makes a cursor known as DBMS using declare……cursor statement and assigning the cursor a (compulsory) name.

Syntax for declaring cursor

declare cursor_name cursor for select .... from ...


     declareis a keyword used for declare a new cursor

     cursor_name is the name of the cursor that you want to use

     foris a clause after that you have to told the select statement

2)      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.

Syntax for opening cursor

open cursor_name

3)      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.

Syntax for fetching cursor

fetch cursor_name

4)      Once an application has processed all the rows of a table or the fetch operation is to be positioned on a non-existing row. The final step involves closing the cursor using the close statement.

Example which demonstrate the use of cursor

declare stu_cursor cursor for select * from Student -- A cursor is declared

open stu_cursor -- after this steps cursor is opened.

fetch stu_cursor --Read one row at a time and display the value of row.Again and again executing this statement will read next rows.

Implementing Concept of Cursor in SQL Server

close stu_cursor --close the open cursor

Scrollable Cursor

Programmers may declare cursor as scrollable or not scrollable. The scrollablity indicates the direction in which a cursor can move. With a non scrollable cursor, also known as forward only can fetch each row at most one.

A programmer may position a scrollable cursor anywhere in the result set using the fetch SQL statement. The keyword scroll must be specified when declaring the cursor. The default is non-scroll cursor.

Syntax for declaring scrollable cursor

declare cursor_name senstivity scroll cursor for select .... from ...

The target position for a scrollable cursor can be specified relative to the current cursor positions or absolute from beginning of the result sets.

Syntax for fetching records in cursor

fetch [next/prior/first/last] from cursor_name  

fetch absolute n from cursor_name

fetch relative n from cursor_name

Scrollable cursor can potentially access the same row in the result set multiple times. Thus, data modification (insert, update, delete)  operations from other transaction could have an impact on the result set. A cursor can be sensitive or insensitive to such modification.

Example which demonstrate the use of Scrollable cursor

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

Implementing Concept of Cursor in SQL Server


close cur_std

Note: For efficiency point of view it is advisable not to use the concept of the cursor until you exactly needs for processing single row at a time.


Don't want to miss updates? Please click the below button!

Follow MindStick