nested fetch

kako
kako used Ask the Experts™
on
does mssql allow nested fetch? here is my code

      declare      cursor1      cursor for      select ..........
                 open cursor1
      WHILE 1=1
      BEGIN
            fetch next from cursor1   into       .........
            if  @@fetch_status <> 0      break
            declare      cursor2      cursor for      select ..................
                       open cursor2
            WHILE 1=1
            BEGIN
                  fetch next from cursor2   into        ............
                  if  @@fetch_status <> 0      break
select ..........
            END
            close            cursor2
            deallocate       cursor2
      END
      close            cursor1
      deallocate       cursor1

is code correct?
select outputs one row only, whereas table contains 3,
how server know which @@fetch_status to check?

Thanks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2006

Commented:
The @@FETCH_STATUS returns the status of the last cursor FETCH statement so it will always be with respect to the most recent fetch next statement.

Author

Commented:
So, nested fetch is allowed?
why get I ore row only, not all 3?
where conditions is ok
Top Expert 2006
Commented:
Which cursor (inner or outer) shows only 1 rows while it should show 3 rows? What happens if you run that cursor alone (not as nested)?

Author

Commented:
all works fine, thanks

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial