SQL - @@FETCH_STATUS

---

---

The SQL @@FETCH_STATUS function is used to retrieve the most recent opened cursor's current fetch status. This function is non-deterministic and is a global function for all cursors in the application. Because the outcome is uncertain.

For example, a user might run a FETCH statement from one cursor, then utilise a stored procedure to open and handle the output from another cursor. Rather than the FETCH statement that was executed before the stored procedure was called, @@FETCH STATUS reflects the last FETCH that was executed inside the stored procedure after control is returned from the called stored procedure.

The SQL @@FETCH_STATUS function return the integer values as shown below −

Syntax

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

@@FETCH_STATUS

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 use while loop to control the cursor activities of @@fetch_status by using the following the query with above created table −

DECLARE Work CURSOR FOR  
SELECT Name,Age 
FROM Workers;  
OPEN Work;  
FETCH NEXT FROM Work;  
WHILE @@FETCH_STATUS =0  
   BEGIN  
      FETCH NEXT FROM Work
   END;

Output

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

+--------+------+
| Name   | Age  |
+--------+------+
| Khilan | 25   |
+--------+------+
+--------+------+
| Name   | Age  |
+--------+------+
|Kaushik | 23   |
+--------+------+
+--------+------+
| Name   | Age  |
+--------+------+
|Chaitali| 25   |
+--------+------+
+--------+------+
| Name   | Age  |
+--------+------+
| Hardik | 27   |
+--------+------+
+--------+------+
| Name   | Age  |
+--------+------+
| Komal  | 22   |
+--------+------+
+--------+------+
| Name   | Age  |
+--------+------+
|        |      |
+--------+------+

Example

Let's look into the following example, where we are going to declare the cursor with a select query whic contain no columns and select only NULL by using the following query −

DECLARE Work CURSOR FOR 
SELECT null FROM INFORMATION_SCHEMA.TABLES
OPEN Work 
SELECT fetch_status from sys.dm_exec_cursors(@@SPID) WHERE name = 'Work'

Output

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

+------------------------------------+
|                        fetch_status|
+------------------------------------+
|                                -9  |
+------------------------------------+

Example

Let's look at the following query, where we are going to create a demo table, insert a value, perform the fetch_status and drop for the table, and check the result by using the following query −

DECLARE @Id int
CREATE TABLE Welcome (
   Id int not null PRIMARY KEY
)
INSERT INTO Welcome (Id) VALUES (1),(2) 
DECLARE Work CURSOR KEYSET FOR SELECT Id FROM Welcome ORDER BY Id
OPEN Work
FETCH NEXT FROM Work INTO @Id
DELETE FROM Welcome WHERE Id = 2 
FETCH NEXT FROM Work INTO @Id  
SELECT @@FETCH_STATUS as [Fetch_Status]
DROP TABLE Welcome
CLOSE Work
DEALLOCATE Work

Output

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

+------------------------------------+
|                        Fetch_Status|
+------------------------------------+
|                                 -2 |
+------------------------------------+

Example

Let's look at the following query, where we are going to perform the fetch_status and read all the rows in the cursor variable by using the following query −

DECLARE Work CURSOR  FOR 
   SELECT Top 2 Name FROM Workers
OPEN Work
SELECT CURSOR_STATUS('global','Work') AS 'isReady?'
fetch next from Work;
while @@FETCH_STATUS = 1
begin
   fetch next from Work;
end
CLOSE Work
SELECT abs(CURSOR_STATUS('global','Work')) AS 'isCursorClosed?'
DEALLOCATE Work

Output

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

+-------------------+      
|          isReady? |
+-------------------+
|               1   |
+-------------------+
+-------------------+
|              Name |
+-------------------+
|          Ramesh   |
+-------------------+
+-------------------+      
|   isCursorClosed? |
+-------------------+
|               1   |
+-------------------+