nested cursors? @@FETCH_STATUS

Is there only one instance of @.@.FETCH_STATUS in T-SQL procs or triggers?
If I have two nested loops and I am using @.@.FETCH_STATUS to see when I am at
the end of the rowset....will the internal loop screw things up for the
external loop? Do I need to save @.@.FETCH_STATUS off to another variable and
use that to control the loop?
Will this work or fail:
DECLARE vcursor cursor local for
select i.id from inserted
Open vcursor
FETCH NEXT FROM vcursor into @.id
WHILE @.@.FETCH_STATUS=0
BEGIN
declare acursor cursor local for
select classcode FROM CLASSES WHERE ID=@.ID
Open acursor
FETCH NEXT FROM acursor into @.aclasscode
WHILE @.@.FETCH_STATUS=0
BEGIN
-- do processing
--
FETCH NEXT FROM acursor into @.aclasscode
END
CLOSE acursor
DEALLOCATE acursor
END
FETCH NEXT FROM vcursor into @.id
END
CLOSE vcursor
DEALLOCATE vcursor
eg will the internal loop reaching the last recods and setting
@.@.FETCH_STATUS=-1 cause the external loop to finish as well or do they each
have their own 'instance' of @.@.FETCH_STATUS
Al Blake, Canberra, AustraliaYour code will work fine. You are fetching the records in each of the loop
and @.@.FETCH_STATUS contains the latest value. So it won't make any problem
Babu M K
Comat Techonologies Pvt. Ltd.
"Al Blake" <al@._delete_this_.blakes.net> wrote in message
news:%231YQkILFFHA.3200@.TK2MSFTNGP10.phx.gbl...
> Is there only one instance of @.@.FETCH_STATUS in T-SQL procs or triggers?
> If I have two nested loops and I am using @.@.FETCH_STATUS to see when I am
at
> the end of the rowset....will the internal loop screw things up for the
> external loop? Do I need to save @.@.FETCH_STATUS off to another variable
and
> use that to control the loop?
> Will this work or fail:
> DECLARE vcursor cursor local for
> select i.id from inserted
> Open vcursor
> FETCH NEXT FROM vcursor into @.id
> WHILE @.@.FETCH_STATUS=0
> BEGIN
> declare acursor cursor local for
> select classcode FROM CLASSES WHERE ID=@.ID
> Open acursor
> FETCH NEXT FROM acursor into @.aclasscode
> WHILE @.@.FETCH_STATUS=0
> BEGIN
> -- do processing
> --
> FETCH NEXT FROM acursor into @.aclasscode
> END
> CLOSE acursor
> DEALLOCATE acursor
> END
> FETCH NEXT FROM vcursor into @.id
> END
> CLOSE vcursor
> DEALLOCATE vcursor
> eg will the internal loop reaching the last recods and setting
> @.@.FETCH_STATUS=-1 cause the external loop to finish as well or do they
each
> have their own 'instance' of @.@.FETCH_STATUS
> Al Blake, Canberra, Australia
>|||On Thu, 17 Feb 2005 16:32:56 +1100, Al Blake wrote:

>Is there only one instance of @.@.FETCH_STATUS in T-SQL procs or triggers?
>If I have two nested loops and I am using @.@.FETCH_STATUS to see when I am a
t
>the end of the rowset....will the internal loop screw things up for the
>external loop? Do I need to save @.@.FETCH_STATUS off to another variable and
>use that to control the loop?
>Will this work or fail:
Hi Al,
As Babu said: this will work. But it will probably be S-L-O-W.
From your code, I see absolutely no reason to use two nested cursors. You
can just do it in one cursor. Depending on what "-- do processing" really
is, you might even be able to do it without cursors at all.
Here's a version with just one cursor. If you need help to create a
completely set-based version, post some more information about what this
code actually does - and check out www.aspfaq.com/5006 for how to provide
the information.
DECLARE vcursor cursor local for
SELECT i.id, c.classcode
FROM inserted AS i
INNER JOIN classes AS c
ON c.ID = i.ID
Open vcursor
FETCH NEXT FROM vcursor into @.id, @.aclasscode
WHILE @.@.FETCH_STATUS=0
BEGIN
-- do processing
--
FETCH NEXT FROM vcursor into @.id
END
CLOSE vcursor
DEALLOCATE vcursor
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)