Microsoft SQL Server Cursors
 SQL
Server cursors are database objects used to manipulate data in a set on a
row-by-row basis. You can fetch cursor rows and perform operations on them
in a loop just like using any looping mechanism found in any other
programming language. Before you can use a cursor, you need to declare it.
Tune slow SQL Server code and eliminate blocks and
deadlocks
Import & export SQL Server data (CSV, Excel, XML, HTML)
Compare & synchronize schemas & data (Free For SQL Server
Express)
DECLARE CURSOR
The DECLARE CURSOR command defines the attributes of a
Transact-SQL server cursor, such as its scrolling behavior and the query
used to build the result set on which the cursor operates. DECLARE CURSOR
accepts both a syntax based on the SQL-92 standard and a syntax using a set
of Transact-SQL extensions.
SQL-92 Syntax
DECLARE cursor_name [ INSENSITIVE ] [ SCROLL ] CURSOR
FOR select_statement
[ FOR { READ ONLY | UPDATE [ OF column_name [ ,...n ] ] } ]
Transact-SQL extended syntax
DECLARE cursor_name CURSOR
[ LOCAL | GLOBAL ]
[ FORWARD_ONLY | SCROLL ]
[ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ]
[ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ]
[ TYPE_WARNING ]
FOR select_statement
[ FOR UPDATE [ OF column_name [ ,...n ] ] ]
SQL-92 arguments
cursor_name is the name of the Transact-SQL server cursor defined. cursor_name
must conform to the rules for identifiers.
INSENSITIVE defines a cursor that makes a temporary copy of the data to be used by the
cursor. All requests to the cursor are answered from this temporary table in tempdb; therefore, modifications made to base tables are not reflected in
the data returned by fetches made to this cursor, and this cursor does not
allow modifications. When SQL-92 syntax is used, if INSENSITIVE is omitted,
committed deletes and updates made to the underlying tables (by any user)
are reflected in subsequent fetches.
SCROLL specifies that all fetch options (FIRST, LAST, PRIOR, NEXT, RELATIVE,
ABSOLUTE) are available. If SCROLL is not specified in an SQL-92 DECLARE
CURSOR, NEXT is the only fetch option supported. SCROLL cannot be specified
if FAST_FORWARD is also specified.
select_statement is a standard SELECT statement that defines the result set of the cursor.
The keywords COMPUTE, COMPUTE BY, FOR BROWSE, and INTO are not allowed
within select_statement of a cursor declaration.
Microsoft® SQL Server™ implicitly converts the cursor to another type if
clauses in select_statement conflict with the functionality of the requested
cursor type.
READ ONLY prevents updates made through this cursor. The cursor cannot be referenced
in a WHERE CURRENT OF clause in an UPDATE or DELETE statement. This option
overrides the default capability of a cursor to be updated.
UPDATE [OF column_name [,...n]] defines updatable columns within the cursor. If OF column_name [,...n] is
specified, only the columns listed allow modifications. If UPDATE is
specified without a column list, all columns can be updated.
Transact-SQL extended arguments
cursor_name is the name of the Transact-SQL server cursor defined. cursor_name
must conform to the rules for identifiers.
LOCAL specifies that the scope of the cursor is local to the batch, stored
procedure, or trigger in which the cursor was created. The cursor name is
only valid within this scope. The cursor can be referenced by local cursor
variables in the batch, stored procedure, or trigger, or a stored procedure
OUTPUT parameter. An OUTPUT parameter is used to pass the local cursor back
to the calling batch, stored procedure, or trigger, which can assign the
parameter to a cursor variable to reference the cursor after the stored
procedure terminates. The cursor is implicitly deallocated when the batch,
stored procedure, or trigger terminates, unless the cursor was passed back
in an OUTPUT parameter. If it is passed back in an OUTPUT parameter, the
cursor is deallocated when the last variable referencing it is deallocated
or goes out of scope.
GLOBAL specifies that the scope of the cursor is global to the connection. The
cursor name can be referenced in any stored procedure or batch executed by
the connection. The cursor is only implicitly deallocated at disconnect.
Note If neither GLOBAL or LOCAL is specified, the default is controlled by
the setting of the default to local cursor database option. In SQL Server
version 7.0, this option defaults to FALSE to match earlier versions of SQL
Server, in which all cursors were global. The default of this option may
change in future versions of SQL Server.
FORWARD_ONLY specifies that the cursor can only be scrolled from the first to the last
row. FETCH NEXT is the only supported fetch option. If FORWARD_ONLY is
specified without the STATIC, KEYSET, or DYNAMIC keywords, the cursor
operates as a DYNAMIC cursor. When neither FORWARD_ONLY nor SCROLL is
specified, FORWARD_ONLY is the default, unless the keywords STATIC, KEYSET,
or DYNAMIC are specified. STATIC, KEYSET, and DYNAMIC cursors default to
SCROLL. Unlike database APIs such as ODBC and ADO, FORWARD_ONLY is supported
with STATIC, KEYSET, and DYNAMIC Transact-SQL cursors. FAST_FORWARD and
FORWARD_ONLY are mutually exclusive; if one is specified the other cannot be
specified.
STATIC defines a cursor that makes a temporary copy of the data to be used by the
cursor. All requests to the cursor are answered from this temporary table in tempdb; therefore, modifications made to base tables are not reflected in
the data returned by fetches made to this cursor, and this cursor does not
allow modifications.
KEYSET specifies that the membership and order of rows in the cursor are fixed when
the cursor is opened. The set of keys that uniquely identify the rows is
built into a table in tempdb known as the keyset. Changes to nonkey values
in the base tables, either made by the cursor owner or committed by other
users, are visible as the owner scrolls around the cursor. Inserts made by
other users are not visible (inserts cannot be made through a Transact-SQL
server cursor). If a row is deleted, an attempt to fetch the row returns an
@@FETCH_STATUS of -2. Updates of key values from outside the cursor resemble
a delete of the old row followed by an insert of the new row. The row with
the new values is not visible, and attempts to fetch the row with the old
values return an @@FETCH_STATUS of -2. The new values are visible if the
update is done through the cursor by specifying the WHERE CURRENT OF clause.
DYNAMIC defines a cursor that reflects all data changes made to the rows in its
result set as you scroll around the cursor. The data values, order, and
membership of the rows can change on each fetch. The ABSOLUTE fetch option
is not supported with dynamic cursors.
FAST_FORWARD specifies a FORWARD_ONLY, READ_ONLY cursor with performance optimizations
enabled. FAST_FORWARD cannot be specified if SCROLL or FOR_UPDATE is also
specified. FAST_FORWARD and FORWARD_ONLY are mutually exclusive; if one is
specified the other cannot be specified.
READ_ONLY prevents updates made through this cursor. The cursor cannot be referenced
in a WHERE CURRENT OF clause in an UPDATE or DELETE statement. This option
overrides the default capability of a cursor to be updated.
SCROLL_LOCKS specifies that positioned updates or deletes made through the cursor are
guaranteed to succeed. Microsoft® SQL Server™ locks the rows as they are
read into the cursor to ensure their availability for later modifications. SCROLL_LOCKS cannot be specified if FAST_FORWARD is also specified.
OPTIMISTIC specifies that positioned updates or deletes made through the cursor do not
succeed if the row has been updated since it was read into the cursor. SQL
Server does not lock rows as they are read into the cursor. It instead uses
comparisons of timestamp column values, or a checksum value if the table has
no timestamp column, to determine whether the row was modified after it was
read into the cursor. If the row was modified, the attempted positioned
update or delete fails. OPTIMISTIC cannot be specified if FAST_FORWARD is
also specified.
TYPE_WARNING specifies that a warning message is sent to the client if the cursor is
implicitly converted from the requested type to another.
select_statement is a standard SELECT statement that defines the result set of the cursor.
The keywords COMPUTE, COMPUTE BY, FOR BROWSE, and INTO are not allowed
within select_statement of a cursor declaration.
SQL Server implicitly converts the cursor to another type if clauses in
select_statement conflict with the functionality of the requested cursor
type.
FOR UPDATE [OF column_name [,...n]] defines updatable columns within the cursor. If OF column_name [,...n] is
supplied, only the columns listed allow modifications. If UPDATE is
specified without a column list, all columns can be updated, unless the
READ_ONLY concurrency option was specified.
Remarks
DECLARE CURSOR defines the attributes of a Transact-SQL server cursor, such
as its scrolling behavior and the query used to build the result set on
which the cursor operates. The OPEN statement populates the result set, and
FETCH returns a row from the result set. The CLOSE statement releases the
current result set associated with the cursor. The DEALLOCATE statement
releases the resources used by the cursor.
The first form of the DECLARE CURSOR statement uses the SQL-92 syntax for
declaring cursor behaviors. The second form of DECLARE CURSOR uses
Transact-SQL extensions that allow you to define cursors using the same
cursor types used in the database API cursor functions of ODBC, ADO, and
DB-Library.
You cannot mix the two forms. If you specify the SCROLL or INSENSITIVE
keywords before the CURSOR keyword, you cannot use any keywords between the
CURSOR and FOR select_statement keywords. If you specify any keywords
between the CURSOR and FOR select_statement keywords, you cannot specify
SCROLL or INSENSITIVE before the CURSOR keyword.
If a DECLARE CURSOR using Transact-SQL syntax does not specify READ_ONLY,
OPTIMISTIC, or SCROLL_LOCKS, the default is as follows:
If the SELECT statement does not support updates (insufficient permissions,
accessing remote tables that do not support updates, and so on), the cursor
is READ_ONLY.
STATIC and FAST_FORWARD cursors default to READ_ONLY.
DYNAMIC and KEYSET cursors default to OPTIMISTIC.
Cursor names can be referenced only by other Transact-SQL statements. They
cannot be referenced by database API functions. For example, after declaring
a cursor, the cursor name cannot be referenced from OLE DB, ODBC, ADO, or
DB-Library functions or methods. The cursor rows cannot be fetched using the
fetch functions or methods of the APIs; the rows can be fetched only by
Transact-SQL FETCH statements.
After a cursor has been declared, these system stored procedures can be used
to determine the characteristics of the cursor.
System stored procedure description
sp_cursor_list: Returns a list of cursors currently visible on the connection
and their attributes.
sp_describe_cursor: Describes the attributes of a cursor, such as whether it
is a forward-only or scrolling cursor.
sp_describe_cursor_columns: Describes the attributes of the columns in the
cursor result set.
sp_describe_cursor_tables: Describes the base tables accessed by the cursor.
Variables may be used as part of the select_statement that declares a
cursor. Cursor variable values do not change after a cursor is declared. In
SQL Server version 6.5 and earlier, variable values are refreshed every time
a cursor is reopened.
Permissions
DECLARE CURSOR permissions default to any user that has SELECT permissions
on the views, tables, and columns used in the cursor.
Sources include the Microsoft website
|
