Navi's

Necessity, the mother of invention

  • Visitor Map

    Locations of Site Visitors
  • Flag Counter

    free counters

How to check CURSOR Status in SQL Server?

Posted by Navi's on June 8, 2012


We can check the cursor status using bellow scalar function. It will return smallint value


SELECT CURSOR_STATUS('global','cursor_name')

It will return single value and each value have different meaning. Below we can find the meaning of returned value

Return value Cursor name Cursor variable

1

The result set of the cursor has at least one row.For insensitive and keyset cursors, the result set has at least one row.

For dynamic cursors, the result set can have zero, one, or more rows.

The cursor allocated to this variable is open.

For insensitive and keyset cursors, the result set has at least one row.

For dynamic cursors, the result set can have zero, one, or more rows.

0 The result set of the cursor is empty.* The cursor allocated to this variable is open, but the result set is definitely empty.*
-1 The cursor is closed. The cursor allocated to this variable is closed.
-2 Not applicable. Can be:No cursor was assigned to this OUTPUT variable by the previously called procedure.

A cursor was assigned to this OUTPUT variable by the previously called procedure, but it was in a closed state upon completion of the procedure. Therefore, the cursor is deallocated and not returned to the calling procedure.

There is no cursor assigned to a declared cursor variable.

-3 A cursor with the specified name does not exist. A cursor variable with the specified name does not exist, or if one exists it has not yet had a cursor allocated to it.

Reference URL: http://bit.ly/KDcw3O

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: