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 | +-------------------+