SQL - @@CURSOR_ROWS

---

---

The SQL @@CURSOR_ROWS function is utilized to determine the number of rows in the current cursor. This is helpful if we are using one or more cursors in the procedures. It's important to keep in mind that this function is non-deterministic. As a result, we must employ these functions along with additional reasoning to decide how the program should proceed.

The row count or current state of the most recently opened cursor object is returned by @@CURSOR_ROWS() function. It returns an integer value for the number of rows or any other value shown in the table below.

Syntax

Following is the syntax of the SQL @@CURSOR_ROWS() function −

@@CURSOR_ROWS

Parameters

It doesn't accept any kind of parameters.

Example

Let's create a table named Workers that we are going to use further in our examples by using the following query −

CREATE TABLE Workers(
   ID   INT              NOT NULL,
   NAME VARCHAR (20)     NOT NULL,
   AGE  INT              NOT NULL,
   ADDRESS  CHAR (25) ,
   SALARY   DECIMAL (18, 2),       
   PRIMARY KEY (ID)
);

Now, let us insert some records in the Workers table using INSERT statements as shown in the query below −

INSERT INTO Workers VALUES (1, 'Ramesh', 32, 'Ahmedabad', 2000.00 );
INSERT INTO Workers VALUES (2, 'Khilan', 25, 'Delhi', 1500.00 );
INSERT INTO Workers VALUES (3, 'kaushik', 23, 'Kota', 2000.00 );
INSERT INTO Workers VALUES (4, 'Chaitali', 25, 'Mumbai', 6500.00 );
INSERT INTO Workers VALUES (5, 'Hardik', 27, 'Bhopal', 8500.00 );
INSERT INTO Workers VALUES (6, 'Komal', 22, 'MP', 4500.00 );

Verification

Let's check whether the table has been created successfully or not by using the following query −

SELECT * FROM Workers;

Output

On executing the above query, it will generate the output as shown below −

+----+----------+-----+-----------+---------+
| ID | NAME     | AGE | ADDRESS   | SALARY  |
+----+----------+-----+-----------+---------+
|  1 | Ramesh   |  32 | Ahmedabad | 2000.00 |
|  2 | Khilan   |  25 | Delhi     | 1500.00 |
|  3 | kaushik  |  23 | Kota      | 2000.00 |
|  4 | Chaitali |  25 | Mumbai    | 6500.00 |
|  5 | Hardik   |  27 | Bhopal    | 8500.00 |
|  6 | Komal    |  22 | MP        | 4500.00 |
+----+----------+-----+-----------+---------+

Example

In the following example, we are going to create and execute the cursor in our table named Workers by using the following query −

DECLARE Work CURSOR FOR 
SELECT Name FROM workers 
OPEN Work
FETCH NEXT FROM Work

Output

When we execute the above query, the output is obtained as follows −

+--------------+
|                               Name |
+------------------------------------+
|                             Ramesh |
+------------------------------------+

Example

Let's look into the another scenario where we are going to close the cursor and deallocate and checking result by using the following query −

SELECT @@CURSOR_ROWS
CLOSE Work
DEALLOCATE Work

Output

When we execute the above query, the output is obtained as follows −

+------------------------------------+
|                              Result|
+------------------------------------+
|                                -1  |
+------------------------------------+

Example

Let's look into the following query, where we are going to select @@CURSOR_ROWS, As a result it return the value 'o' because the cursor is not opened −

SELECT @@CURSOR_ROWS;

Output

On executing the above query, the output is displayed as follows −

+------------------------------------+
|                              Result|
+------------------------------------+
|                                  0 |
+------------------------------------+

Example

Let's look into the following query, where we are going to retrive the total no.of rows present in our table Workers by using the following query −

DECLARE Work CURSOR FOR
SELECT TOP 10 Name, age FROM Workers;
SELECT @@CURSOR_ROWS AS 'Total Rows/Status';

Output

On executing the above query, the output is displayed as follows −

+------------------------------------+
|                    TotalRows/Status|
+------------------------------------+
|                                  6 |
+------------------------------------+