Professional SQL Server™ 2005 Programming

15.1. What Is a Cursor?

Cursors are a way of taking a set of data and being able to interact with a single record at atime. It doesn't happen nearly as often as one tends to think, but there are indeed times where you just can't obtain the results you want to by modifying or even selecting the data in an entire set. The set is generated by something all of the rows have in common (as defined by a SELECT statement), but then you need to deal with those rows on a one-by-one basis.

The result set that you place in a cursor has several distinct features that set it apart from a normal SELECT statement:

• You declare the cursor separately from actually executing it.

• The cursor and, therefore, its result set are named at declaration — you then refer to it by name.

• The result set in a cursor, once opened, stays open until you close it.

• Cursors have a special set of commands used to navigate the recordset.

While SQL Server has its own engine to deal with cursors, there are actually a few different object libraries that can also create cursors in SQL Server:

• SQL Native Client (used by ADO.NET)

• OLE DB (used by ADO)

• ODBC (used by RDO, DAO, and in some cases, OLE DB/ADO)

• DB-Lib (used by VB-SQL)

These are the libraries that client applications will typically use to access individual records. Each provides it own syntax for navigating the recordset and otherwise managing the cursor. Each, however, shares in the same set of basic concepts, so, once you have got one object model down for cursors, you're ...