@@FETCH_STATUS

hi.
i have a question about @.@.FETCH_STATUS when doing a cursor loop.
i found 2 examples and i want to know what the diffrence is between
them,
the first one i found in the help:
DECLARE Employee_Cursor CURSOR FOR
SELECT LastName, FirstName FROM Northwind.dbo.Employees
OPEN Employee_Cursor
FETCH NEXT FROM Employee_Cursor
WHILE @.@.FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM Employee_Cursor
END
CLOSE Employee_Cursor
DEALLOCATE Employee_Cursor
here it uses @.@.FETCH_STATUS = 0, wich would be while it is successfull.
the next exaple was an example in the QA:
DECLARE <cursor_name, sysname, test_cursor> CURSOR
READ_ONLY
FOR <select_statement, , SELECT au_fname FROM pubs.dbo.authors>
DECLARE @.name varchar(40)
OPEN <cursor_name, sysname, test_cursor>
FETCH NEXT FROM <cursor_name, sysname, test_cursor> INTO @.name
WHILE (@.@.fetch_status <> -1)
BEGIN
IF (@.@.fetch_status <> -2)
BEGIN
-- PRINT 'add user defined code here'
-- eg.
DECLARE @.message varchar(100)
SELECT @.message = 'my name is: ' + @.name
PRINT @.message
END
FETCH NEXT FROM <cursor_name, sysname, test_cursor> INTO @.name
END
CLOSE <cursor_name, sysname, test_cursor>
DEALLOCATE <cursor_name, sysname, test_cursor>
here it first says, while @.@.FETCH_STATUS <> -2 and then says
only if @.@.FETCH_STATUS <> -1
in my world this would result in the same thing, since according to the
help the 3 diffrent statuses is 0, -1 and -2.
what is the diffrence between the two? and wich one is the "right" one
to use?
/Anders0
The FETCH statement was successful.
-1
The FETCH statement failed or the row was beyond the result set.
-2
The row fetched is missing.
-oj
<aebohlin@.gmail.com> wrote in message
news:1141228790.445782.79910@.v46g2000cwv.googlegroups.com...
> hi.
> i have a question about @.@.FETCH_STATUS when doing a cursor loop.
> i found 2 examples and i want to know what the diffrence is between
> them,
> the first one i found in the help:
> DECLARE Employee_Cursor CURSOR FOR
> SELECT LastName, FirstName FROM Northwind.dbo.Employees
> OPEN Employee_Cursor
> FETCH NEXT FROM Employee_Cursor
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> FETCH NEXT FROM Employee_Cursor
> END
> CLOSE Employee_Cursor
> DEALLOCATE Employee_Cursor
> here it uses @.@.FETCH_STATUS = 0, wich would be while it is successfull.
> the next exaple was an example in the QA:
> DECLARE <cursor_name, sysname, test_cursor> CURSOR
> READ_ONLY
> FOR <select_statement, , SELECT au_fname FROM pubs.dbo.authors>
> DECLARE @.name varchar(40)
> OPEN <cursor_name, sysname, test_cursor>
> FETCH NEXT FROM <cursor_name, sysname, test_cursor> INTO @.name
> WHILE (@.@.fetch_status <> -1)
> BEGIN
> IF (@.@.fetch_status <> -2)
> BEGIN
> -- PRINT 'add user defined code here'
> -- eg.
> DECLARE @.message varchar(100)
> SELECT @.message = 'my name is: ' + @.name
> PRINT @.message
> END
> FETCH NEXT FROM <cursor_name, sysname, test_cursor> INTO @.name
> END
> CLOSE <cursor_name, sysname, test_cursor>
> DEALLOCATE <cursor_name, sysname, test_cursor>
> here it first says, while @.@.FETCH_STATUS <> -2 and then says
> only if @.@.FETCH_STATUS <> -1
> in my world this would result in the same thing, since according to the
> help the 3 diffrent statuses is 0, -1 and -2.
> what is the diffrence between the two? and wich one is the "right" one
> to use?
> /Anders
>|||You don't need <>-2 checking for READONLY CURSOR. If the cursor is Ket-Set
driven cursor, you may read non-existing rows. Then you need to cehck for <>
-2
Refer "Cursor Types" in BOL
--
Thanks & Rate the Postings.
-Ravi-
"aebohlin@.gmail.com" wrote:

> hi.
> i have a question about @.@.FETCH_STATUS when doing a cursor loop.
> i found 2 examples and i want to know what the diffrence is between
> them,
> the first one i found in the help:
> DECLARE Employee_Cursor CURSOR FOR
> SELECT LastName, FirstName FROM Northwind.dbo.Employees
> OPEN Employee_Cursor
> FETCH NEXT FROM Employee_Cursor
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> FETCH NEXT FROM Employee_Cursor
> END
> CLOSE Employee_Cursor
> DEALLOCATE Employee_Cursor
> here it uses @.@.FETCH_STATUS = 0, wich would be while it is successfull.
> the next exaple was an example in the QA:
> DECLARE <cursor_name, sysname, test_cursor> CURSOR
> READ_ONLY
> FOR <select_statement, , SELECT au_fname FROM pubs.dbo.authors>
> DECLARE @.name varchar(40)
> OPEN <cursor_name, sysname, test_cursor>
> FETCH NEXT FROM <cursor_name, sysname, test_cursor> INTO @.name
> WHILE (@.@.fetch_status <> -1)
> BEGIN
> IF (@.@.fetch_status <> -2)
> BEGIN
> -- PRINT 'add user defined code here'
> -- eg.
> DECLARE @.message varchar(100)
> SELECT @.message = 'my name is: ' + @.name
> PRINT @.message
> END
> FETCH NEXT FROM <cursor_name, sysname, test_cursor> INTO @.name
> END
> CLOSE <cursor_name, sysname, test_cursor>
> DEALLOCATE <cursor_name, sysname, test_cursor>
> here it first says, while @.@.FETCH_STATUS <> -2 and then says
> only if @.@.FETCH_STATUS <> -1
> in my world this would result in the same thing, since according to the
> help the 3 diffrent statuses is 0, -1 and -2.
> what is the diffrence between the two? and wich one is the "right" one
> to use?
> /Anders
>|||thanks alot.sql