MS SQL Basics Tutorial: declaring cursors

Each cursor must have four components, these four key parts must conform to the following order;

1.DECLARE Cursors

2.OPEN Cursors

3. Fetch information from a cursor

4.CLOSE or deallocate cursors

Usually we use declare to declare a cursor declaring a cursor that consists mainly of the following main elements: Cursor name data source (table and column) Select condition attributes (read-only or modifiable)

The syntax format is as follows:

DECLARE cursor_name [insensitive] [SCROLL] Cursor

For select_statement

[For {READ only | UPDATE [of column_name [,... N]]}]

of which: cursor_name

Refers to the name of the cursor. Insensitive

Indicates that MS SQL SERVER stores the data records selected by the cursor definition in a temporary table (built under the tempdb database). The read operation on the cursor is answered by a temporary table. As a result, modifications to the base table do not affect the data that is extracted by the cursor, that is, the cursor does not change as the contents of the base table change, nor does it pass

Cursor to update the base table. If you do not use the reserved word, updates and deletions to the base table will be reflected in the cursor.

It should also be noted that the cursor automatically sets the INSENSITIVE option when the following conditions occur.

Use a distinct, GROUP by, having UNION statement in a SELECT statement;

Use outer JOIN;

Any table selected has no index;

Treats a real value as a selected column. SCROLL

Indicates that all extraction operations (such as the PRIOR, NEXT, relative, and absolute) are available. If you do not use the reserved word, you can only perform a next fetch. Thus, SCROLL greatly increases the flexibility of extracting data, and can read any row of data records in the result set without having to close

Re-open the cursor. Select_statement

Is the SELECT statement that defines the result set. It should be noted that compute, compu-te by, a for BROWSE, and into statements cannot be used in cursors. READ only