Checking the status from the last fetch

@@sqlstatus contains status information resulting from the last fetch statement for the current user session. @@sqlstatus may contain the following values:

Table 15-4: @@sqlstatus values

Value

Meaning

0

The fetch statement completed successfully.

1

The fetch statement resulted in an error.

2

There is no more data in the result set. This warning occurs if the current cursor position is on the last row in the result set and the client submits a fetch command for that cursor.

@@sqlstatus has no effect on @@error output. For example, the following batch sets @@sqlstatus to 1 by causing the fetch @@error statement to result in an error. However, @@error reflects the number of the error message, not the @@sqlstatus output:

declare csr1 cursor
for select * from sysmessages
for read only
 
open csr1
 
begin
    declare @xyz varchar(255)
    fetch csr1 into @xyz
    select error = @@error
    select sqlstatus = @@sqlstatus
end
Msg 553, Level 16, State 1:
Line 3:
The number of parameters/variables in the FETCH INTO clause does not match the number of columns in cursor ’csr1’ result set.

At this point, the @@error global variable is set to 553, the number of the last generated error. @@sqlstatus is set to 1.

@@fetch_status returns the status of the most recent fetch:

Table 15-5: @@fetch_status

Value

Meaning

0

fetch operation successful.

-1

fetch operation unsuccessful.

-2

Reserved for future use.