Professional SQL Server™ 2005 Programming

A.3. Cursor Functions

There is only one cursor function (CURSOR_STATUS), and it provides information about cursors.

A.3.1. CURSOR_STATUS

The CURSOR_STATUS function allows the caller of a stored procedure to determine if that procedure has returned a cursor and result set. The syntax is as follows:

CURSOR_STATUS
   (
      {'<local>', '<cursor_name>'}
      | {'<global'>, '<cursor_name>'}
      | {'<variable>', '<cursor_variable>'}
   )

local, global, and variable all specify constants that indicate the source of the cursor. Local equates to a local cursor name, global to a global cursor name, and variable to a local variable.

If you are using the cursor_name form then there are four possible return values:

• 1 — The cursor is open. If the cursor is dynamic, its result set has zero or more rows. If the cursor is not dynamic, it has one or more rows.

• 0 — The result set of the cursor is empty.

• −1 — The cursor is closed.

• −3 — A cursor of cursor_name does not exist.

If you are using the cursor_variable form, there are five possible return values:

• 1 — The cursor is open. If the cursor is dynamic, its result set has zero or more rows. If the cursor is not dynamic, it has one or more rows.

• 0 — The result set is empty.

• −1 — The cursor is closed.

• −2 — There is no cursor assigned to the cursor_variable.

• −3 — The variable with name cursor_variable does not exist, or if it does exist, has not had a cursor allocated to it yet.