What are the examples for @@fetch_status value -2 and -9

Question

I need some examples for @@fetch_status values -2 and -9

   0 = The FETCH statement was successful.
  -1 = The FETCH statement failed or the row was beyond the result set.

Here is a sample cursor example

 declare @country varchar(50)
 declare cur_country cursor for 
  select name from global
 open cur_country

fetch next from cur_country into @country
  print @@FETCH_STATUS
 while (@@FETCH_STATUS=0)
begin
insert into country select @country
fetch next from cur_country into @country
end

 close cur_country
 deallocate cur_country.

-2 The row fetched is missing. -9 The cursor is not performing a fetch operation.

basically need scenario where @@FETCH_STATUS gives -2 0r -9

Answer 1

@@FETCH_STATUS = -2 usually happens when some process OUTSIDE of the cursor deletes a row in the table the cursor is based on.

If job 1 opens a cursor and starts looping through records in table1, and while Job 1 is looping, Job 2 comes along and deletes certain records in table1, job 1 might return a -2 when it tries to retrieve a row it was expecting to find (because it was there when the cursor started).

The following Topic contains an example of @@FETCH_STATUS = -2

While researching i found the following explanation from this Topic :

Specifies that the membership and order of rows in the cursor are fixed when the cursor is opened. The set of keys that uniquely identify the rows is built into a table in tempdb known as the keyset. Changes to non key values in the base tables, either made by the cursor owner or committed by other users, are visible as the owner scrolls around the cursor. Inserts made by other users are not visible (inserts cannot be made through a Transact-SQL server cursor). If a row is deleted, an attempt to fetch the row returns an @@FETCH_STATUS of -2. Updates of key values from outside the cursor resemble a delete of the old row followed by an insert of the new row. The row with the new values is not visible and attempts to fetch the row with the old values return an @@FETCH_STATUS of -2. The new values are visible if the update is done through the cursor by specifying the WHERE CURRENT OF clause

And like DVT commented. This Stackoverflow question contains an example on @@FETCH_STATUS = -9

More info about @@FETCH_STATUS can be found at this MSDN article

---

Update

Two months ago, I published an article about @@FETCH_STATUS where I provided some examples that produce -2 and -9 values (The other answer helped me a lot while writing this article):

• An overview of the SQL cursor @@FETCH_STATUS function

Answer 2

Usually, minimal complete verifyable examples have to be part of a good question. Here, I attempt to answer with two of them:

1. A minimal cursor scenario which will yield -9 ("not fetching")

Note that @@FETCH_STATUS itself will never assume this value (it will return 0). Only the internal control structures have this as an initial value for cursors declared and never fetched from (regardless if open or not).

DECLARE [cursor-9] CURSOR FOR SELECT null FROM sys.tables
SELECT [fetch_status] FROM sys.dm_exec_cursors(@@SPID) WHERE name='cursor-9'
DEALLOCATE [cursor-9]

Further reference: https://stackoverflow.com/a/36272354/1132334

2. Minimal cursor scenario which will return -2 ("row missing")

This needs two connections for concurrency. I used two instances of SSMS, connected to an 11.0.6020 with one script window each, connected to the same empty database.

Run this script in the first instance:

DECLARE @@id int

CREATE TABLE tmp41307323 (
  Id int not null PRIMARY KEY
)
INSERT INTO tmp41307323 (Id) VALUES (1),(2)

DECLARE [cursor-2] CURSOR KEYSET FOR SELECT Id FROM tmp41307323 ORDER BY Id
OPEN [cursor-2]
FETCH NEXT FROM [cursor-2] INTO @@id

It will create a table with a primary key, add two rows, and open a keyset cursor on it. Without the KEYSET keyword, you will get -1 instead of -2 because that is how keysets work: SQL Server creates a temporary table in tempdb, which holds only the ordered unique key values for each row the cursor selects. Then, as we fetch, it looks up the keys of the next row to fetch in the temporary table, and selects just that row from the real table. This, and only this, scenario is therefore susceptible to concurrent deletions. Beyond that, it will see changes made to any non-key columns of the source table made while fetching.

Run this script in the second instance:

DELETE FROM tmp41307323 WHERE Id=2

So we remove the row that the cursor in the first instance would expect to see when it fetches next.

Finally, run this script in the first instance (don't disconnect so the cursor will still be in scope):

DECLARE @@id int
FETCH NEXT FROM [cursor-2] INTO @@id
SELECT @@FETCH_STATUS [@@FETCH_STATUS]
CLOSE [cursor-2]
DEALLOCATE [cursor-2]

DROP TABLE tmp41307323

Result:

It would work just the same when run in a single batch, on the same connection, with the DELETE right before the second FETCH. The two-connection setup demonstrates it in a realistic context, assuming a developer aware of the KEYSET keyword would not on purpose delete while fetching, and would not have side-effects in the cursor loop causing such deletions (using both cursors and triggers is about the same abomination as manipulating html with regex anyway).