Cursors (Transact-SQL)
Cursors (Transact-SQL)
Microsoft SQL Server statements produce a complete result set, but there are times when the results are best processed one row at a time. Opening a cursor on a result set allows processing the result set one row at a time. You can assign a cursor to a variable or parameter with a cursor data type.
Cursor operations are supported on these statements:
These system functions and system stored procedures also support cursors:
See Also
DECLARE CURSOR (Transact-SQL)
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 ISO standard and a syntax using a set of Transact-SQL extensions.
Syntax
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 ] ] ]
[;]
What is the use of a cursor in SQL Server?
Cursors are a mechanism to explicitly enumerate through the rows of a result set, rather than retrieving it as such.
However, while they may be more comfortable to use for programmers accustomed to writing While Not RS.EOF Do ...
, they are typically a thing to be avoided within SQL Server stored procedures if at all possible -- if you can write a query without the use of cursors, you give the optimizer a much better chance to find a fast way to implement it.
In all honesty, I've never found a realistic use case for a cursor that couldn't be avoided, with the exception of a few administrative tasks such as looping over all indexes in the catalog and rebuilding them. I suppose they might have some uses in report generation or mail merges, but it's probably more efficient to do the cursor-like work in an application that talks to the database, letting the database engine do what it does best -- set manipulation.