Mahmoud ElMansy: knowledge meant to be free

DECLARE CURSOR (Transact-SQL)

DECLARE CURSOR (Transact-SQL)
Syntax as in MSDN
ISO 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 ] ] ]
[;]

Arguments
cursor_name:
Is the name of the Transact-SQL server cursor defined.
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.
SCROLL:
Specifies that all fetch options (FIRST, LAST, PRIOR, NEXT, RELATIVE, ABSOLUTE) are available. If SCROLL is not specified in an ISO 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.
READ ONLY:
Prevents updates made through this cursor.
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.
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.
Note 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. For more information, see Setting Database Options.
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.
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.
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.
DYNAMIC:
Defines a cursor that reflects all data changes made to the rows in its result set as you scroll around the cursor.
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.
SCROLL_LOCKS:
Specifies that positioned updates or deletes made through the cursor are guaranteed to succeed. 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 or STATIC 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 when the cursor is implicitly converted from the requested type to another.

Notes:
You can use a query hint within a cursor declaration; however, if you also use the FOR UPDATE OF clause, specify OPTION (query_hint) after FOR UPDATE OF.
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.

We can track down cursor by some System stored procedures

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.

Examples:
1– The basic syntax of a cursor is:

-- Declare variable to hold in the current id for the table
DECLARE @ID INT
DECLARE TestCURSOR CURSOR READ_ONLY
FOR
SELECT table_id
FROM TestTable
OPEN TestCURSOR
FETCH NEXT FROM TestCURSOR
INTO @ID
WHILE @@FETCH_STATUS = 0
BEGIN
-- start here to put your SQL code
--sample code print in the output
PRINT CONVERT(VARCHAR,@ID)
--end
FETCH NEXT FROM TestCURSOR
INTO @ID
END
--end of the CURSOR and destroy it
CLOSE TestCURSOR
DEALLOCATE TestCURSOR

2– Nested Cursors
in this example we will make two Cursors and the child cursor will use data from the parent Cursor

DECLARE @ID INT
DECLARE @ID_1 INT
DECLARE @UpdateStatus INT
-- declare ParentCursor
DECLARE ParentCursor CURSOR FOR
SELECT ID
FROM TestTable
-- open ParentCursor
OPEN ParentCursor
FETCH NEXT FROM ParentCursor INTO @EntityId, @BaseId
WHILE @@FETCH_STATUS = 0
BEGIN
-- Start reading data from the ParentCursor
-- declare ChildCursor
DECLARE ChildCursor CURSOR FOR
SELECT ID
FROM TestTable1
WHERE TestTable1.ID = @ID
-- open ChildCursor
OPEN ChildCursor
FETCH NEXT FROM ChildCursor INTO @ID_1
SET @UpdateStatus = @@FETCH_STATUS
WHILE @UpdateStatus = 0
BEGIN
-- Start reading data from the ChildCursor
/*
Your Code Here
*/

FETCH NEXT FROM ChildCursor INTO @ID_1
SET @UpdateStatus = @@FETCH_STATUS
END
-- End and clean up ChildCursor
CLOSE ChildCursor
DEALLOCATE ChildCursor
-- Fetch another reading from ParentCursor
FETCH NEXT FROM ParentCursor INTO @ID
END
-- End and clean up ParentCursor
CLOSE ParentCursor
DEALLOCATE ParentCursor --cleanup ParentCursor

References:

DECLARE CURSOR (Transact-SQL)

Cursors (Transact-SQL)