Here the remearks for @@FETCH_STATUS in BOL:
Because @@FETCH_STATUS is global to all cursors on a connection, use @@FETCH_STATUS carefully.
After a FETCH statement is executed, the test for @@FETCH_STATUS must occur before any other FETCH statement is executed
against another cursor. The value of @@FETCH_STATUS is undefined before any fetches have occurred on the connection.
For example, a user executes a FETCH statement from one cursor, and then calls a stored procedure that opens and processes
the results from another cursor. When control is returned from the called stored procedure, @@FETCH_STATUS reflects the last
FETCH executed in the stored procedure, not the FETCH statement executed before the stored procedure is called.
To retrieve the last fetch status of a specific cursor, query the fetch_status column of the sys.dm_exec_cursors dynamic
management function.>Periodically @@FETCH_STATUS returns a -2. What could be causing this problem? This procedure calls Proc2 inside the loop.
>
>
>declare
>@Ctr int,
>@stor_id int
>
>Set @ctr = 1
>
>declare Temp1 cursor scroll
> for
> select
> stor_id
> from stores with (nolock)
>
>open Temp1
>
>FETCH ABSOLUTE @Ctr FROM Temp1 INTO @stor_id
>
>WHILE @@FETCH_STATUS = 0
> BEGIN
> > EXEC Proc2
>
> set @ctr = @Ctr + 1
>
> FETCH ABSOLUTE @Ctr FROM Temp1 INTO @stor_id
> print '
> print 'sp1 ' + convert(char,@ctr)+convert(char,@@FETCH_STATUS)
> END
>
>CLOSE Temp1
>DEALLOCATE Temp1
>
Against Stupidity the Gods themselves Contend in Vain - Johann Christoph Friedrich von Schiller
The only thing normal about database guys is their tables.