Purpose of @@FETCH_STATUS = -2

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,
Gopiccording to BOL,
0: FETCH statement was successful.
-1: FETCH statement failed or the row was beyond the result set.
-2: Row fetched is missing.
so -2 means that you tried to fetch a row, but the row isn't there anymore.
This can happen because someone has deleted it btween when you created the
cursor, and when you try to fetch that particular row...
"gopi" wrote:

> 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
>
>|||That cursor shouldn't need a check against -2 as it is a read-only cursor. B
ut if you have a key-set
driven cursor (SQL Server stored only the primary key, when fetching, SQL Se
rver 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/
"gopi" <rgopinath@.hotmail.com> wrote in message news:%23uU4aukKFHA.656@.TK2MSFTNGP14.phx.gbl
..
> Hello All,
> I have used Cursor quite a few times. However, I have never got an oppurtu
nity 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...
> 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/
>
> "gopi" <rgopinath@.hotmail.com> wrote in message
> news:%23uU4aukKFHA.656@.TK2MSFTNGP14.phx.gbl...
>sql