This is a new topic in Learn Sql Server series. Cursors are more talked about entity in Sql server. Sometimes some guys asked me how to process rowwise data in Sql Server and I wonder how curious they were about this thing. And yesterday I thought to write about cursors.

What is Cursor?

A cursor is a database object that helps in accessing and maniputlating data in a given result set. The main advantage of cursors is that you can process dta row-by-row. A result set is defined as a collection of rows obtained from a SELECT statement that meet the criteria specified in the WHERE clause.

Curosrs, threfore, serve as a mechanism for applications to operate on a single row or a set of rows. Cursors enable the processing of rows in the result set in the following ways:

• Allow specific rows to be retrieved from the result set
• Allow the current row in the result set to be modified.
• Help navigate from the current row in the result set to a different row.

• Allow data modified by other users to be visible in the result set.

Structure of cursors

The following tasks need to be performed while using a cursor in SQL Server:

• The cursor needs to be defined and its attributes need to be set.
• The cursor needs to be opened.
• The required rows need to be fetched from the cursor. Fetch refers to the process of retrieving a row from the result set.
• The data in the current row of the cursor can be modified, if required.
• The cursor needs to be closed.
• The cursor should be dealocated. This is a good practice as resources used by the cursor are released.

Declaring Cursors

You can define a cursor and its characteristics set by using the DECLARE CURSOR statement.

Syntax:

DECLARE cursor_name [INSENSITIVE][SCROLL] CURSOR

FOR {select statement}

[FOR {READ ONLY | UPDATE [ OF column_list ]}]

where,

cursor_name is the name of the cursor to be defined. The cursor name must follow the rules for the identifier.

INSENSITIVE causes the result set to be stored in the tempdb database and does not permit the result set of the cursor to be modified.

SCROLL specifeds that the cursor can be positioned forward or backward to read data. All the subsquent fetches display modifications that have been made to the underlying tables as lon as the cursor is not declared using the INSENSITIVE option.

select_statement is a standard SELECT statement used for retrieving rows from one or more tables.

READ ONLY prevents modifications in the result set of the cursor.

UPDATE [OF column_list] defines the updateable columns within the cursor. If the OF column_list option is used, then only the listed columns can be updated, otherwise all columns are updateable, unless the cursor has been defined as READ ONLY.

Opening Cursors

You can open a previously declared cursor by using the OPEN statement.

Syntax,

OPEN cursor_name

Where,

cursor_name is the cursor that is to be opened.

Fetching Data

After opening the cursor, you can retrieve a specific row from the result set of the cursor, SQL Server 2000 provides the FETCH statement to accomplish this task.

Sysntax

FETCH [[NEXT | PRIOR | FIRST | LAST | ABSOLUTE N | RELATIVE N]] FROM cursor_name

[INTO @variable_name [...n]]

where,

NEXT moves the cursor one row forward in the result set. This is the default action performed by the FETCH statement.

PRIOR returns the previous row in the result set.

FIRST returns the first row in the result set.

LAST returns the last row in the result set.

ABSOLUTE n returns the n^th row in the result set. If n is a positive value, then rows are counted from the top of the result set, otherwise rows are counted backward from the last row in the result set.

RELATIVE n returns the n^th row after the currently fetched row. If n is a negative value, the returned row will be the n^th row counting backward from the relative position of the cursor.

FROM cursor_name is the name of the cursor from which the row(s) is to be fetched.

The number and the datatype of the host variables must be identical to that of the columns used in the FETCH statement.

When a FETCH statement is executed, the cursor is said to be positioned on the rowthat has been fetched. The fetched row is called the current row.

@@fetch_status

It returns the status of the last fetch statement executed.

@@fetch_status Description

0 Successful execution of the fetch

statement.

-1 Unsuccessful execution of the fetch

statement.

-2 Row being fetched is missing.

Deallocate Cursors

You can remove the definition of the cursor that had been defined by the DECLARE cursor statement. All the resources held by the cursor are released. A cursor can be deallocated using the DEALLOCATE statement.

Syntax

DEALLOCATE cursor_name

where,

cursor_name is the name of the cursor that is already declared.

 

I think above articles will certainly help the readers to create cursors and if you want more information than can asked questions. bye