That cursor shouldn't need a check against -2 as it is a read-only cursor. But if you have a key-set
driven cursor (SQL Server stored only the primary key, when fetching, SQL Server uses the PK to
fetch the other columns, you might end up navigating to a row which has been deleted in the table.
Hence @@FETCH_STATUS = -2.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/


[quoted text, click to view]

Hello All,

I have used Cursor quite a few times. However, I have never got an
oppurtunity to use @@FETCH_STATUS = -2
though I have seen it being used quite a few times. Would any know why and
when this is needed. The BOL is not
clear on this also.

For example, in this article, why is @@FETCH_STATUS = -2 being used ?
http://www.sqlteam.com/itemprint.asp?ItemID=5761


FETCH NEXT FROM C1 INTO @AMT
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
SET @SUM_AMT = @SUM_AMT + @AMT
SET @RECS = @RECS + 1
END
FETCH NEXT FROM C1 INTO @AMT
END

Thanks,
Gopi

Thanks Tibor. After reading your response, I checked BOL for KEYSET and
found the following :

Gopi
KEYSET

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 nonkey 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.



"Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.com> wrote in
message news:eKuLi2kKFHA.3916@TK2MSFTNGP14.phx.gbl...
[quoted text, click to view]