Cursor Function

This article is related to cursor function. Here in this article I am trying to mention each function in simple way that you can use it when you are using the cursor.

Cursor function pays a very important rows when you playing with cursor. The cursor function is non deterministic, means it not returns the same result set if you call them with same input value.

 There are three scalar function of the cursor.

1.    @@CURSOR_ROWS

2.    CURSOR_STATUS

3.    @@FETCH_STATUS

 

 

@@CURSOR_ROWS

We can get, how many rows will be return by the cursor currently the last cursor open by the connection. @@CURSOR_ROWS is negative if the last cursor was opened asynchronously. Keyset-driver or static cursors are opened asynchronously if the value for sp_configurecursor threshold is greater than 0, and the number of rows in the cursor result set is greater than the cursor threshold.

Return value	Description
-m	The cursor is populated asynchronously. The value returned (-m) is the number of rows currently in the keyset.
-1	The cursor is dynamic. Because dynamic cursors reflect all changes, the number of rows that qualify for the cursor is constantly changing. It can never be definitely stated that all qualified rows have been retrieved.
0	No cursors have been opened, no rows qualified for the last opened cursor, or the last-opened cursor is closed or deallocated.
n	The cursor is fully populated. The value returned (n) is the total number of rows in the cursor.

 

SELECT @@CURSOR_ROWS;

DECLARE Name_Cursor CURSOR FOR

              SELECT itemdescr ,@@CURSOR_ROWS FROM itemmaster where itemcode='0000002';

OPEN Name_Cursor;

 

FETCH NEXT FROM Name_Cursor;

SELECT @@CURSOR_ROWS;

 

CLOSE Name_Cursor;

DEALLOCATE Name_Cursor;

GO   

 

@@FETCH_STATUS

It returns the status of last cursor FETCH

 

Return value	Description
0	The FETCH statement was successful.
-1	The FETCH statement failed or the row was beyond the result set.
-2	The row fetched is missing.

 

DECLARE my_cur CURSOR FOR

                      SELECT id, itemdescr

                      FROM   itemmaster;

OPEN my_cur;

 

FETCH NEXT FROM my_cur;

 

WHILE @@FETCH_STATUS = 0

   BEGIN

      <.....>

      FETCH NEXT FROM my_cur;

   END;

  

CLOSE my_cur;

DEALLOCATE my_cur;

GO

 

 

CURSOR_STATUS

Determines that the cursor is still open or NOT. When you are using multiple cursor, it is very useful.

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.

 

IF (SELECT CONVERT(DECIMAL,CURSOR_STATUS('global','my_CUR')))>0

        BEGIN

          CLOSE my_CUR  

              DEALLOCATE my_CUR

        END

 

Hope the article is quite informative and thanking you to provide your valuable time on it.

 

Posted by: MR. JOYDEEP DAS