Nested Cursors

Question

Nested Cursors

william oliveri

Valued Member

Points: 65
More actions
March 27, 2003 at 9:39 am

#81729

Hello all,
I'm trying to do nested loops and it seems I can only get one loop out of the outer loop.
here's the code:
declare pncursor1 cursor for select distinct col001 from basic order by
col001
declare pncursor2 cursor for select distinct col001 from basic order by
col001
declare @counter int
declare @pname1 varchar(50)
declare @pname2 varchar(50)
select @counter = 2000
open pncursor1
fetch next from pncursor1 into @pname1
while (@@fetch_status = 0)
begin
fetch next from pncursor1 into @pname1
print '...................p1'
open pncursor2
fetch next from pncursor2 into @pname2
while (@@fetch_status = 0)
begin
fetch next from pncursor2 into @pname2
print 'p2'
end
close pncursor2
end
close pncursor1
deallocate pncursor1
deallocate pncursor2
Output (partial): I'm only getting one p1 (outside loop).
...................p1
p2
p2
p2
p2
p2
p2
p2
p2
p2
p2
p2
p2
p2
p2
p2
p2
p2
p2
p2
p2
p2
p2
p2
p2
p2

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

@@fetch_status is the problem here; it doesn't revert back to the outer loops 'previous' fetch status after it complete's the inner loop.

wouldn't @@fetch_status only allow the inner loop to run, then when you come out of the inner loop, since it is -1, the outer loop would terminate?

i think you need to put the inner and outer fetch statuses into variables, or do a new fetch_status for the outer loop AFTER the inner loop complete's, instead of at the top of the begin..end

lowell@stormrage.com

Edited by - lowell@stormrage.com on 03/27/2003 10:06:53 AM

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

David Burrows SSC Guru Points: 65142 More actions · Answer 2

I think you have a logic problem, try

open pncursor1

fetch next from pncursor1 into @pname1

while (@@fetch_status = 0)

begin

print '...................p1'

open pncursor2

fetch next from pncursor2 into @pname2

while (@@fetch_status = 0)

begin

print 'p2'

fetch next from pncursor2 into @pname2

end

close pncursor2

fetch next from pncursor1 into @pname1

end

close pncursor1

deallocate pncursor1

deallocate pncursor2

Far away is close at hand in the images of elsewhere.
Anon.