Nested Cursors

I was working on a problem at work to find items within a given property, within a given aspect, within a... (you get the point) But what to do in the case that @@fetch_status <> 0 would it break all levels of the nesting? It can if you do it wrong. There are several examples on the web, but here is the best way to conquer this relatively simple query.

First you need to create your variables and cursors. It is recommended to not create your cursors outside of the level you are using them. The best reason is so that you can use LOCAL with your Declare to auto deallocate your cursor. This is a nice feature for efficiency and resource reasons. I used both methods in my query and tested the server impact and saw a noticeable resource difference. The time differences are so minute that there is barely reason to mention it.
Second thing to do is use your fetch at the end of each nest and don't close the cursor till your iteration of the nesting level is completed.
Here is a mock up of a two level nesting. The concept is the same for multiple levels. Hope this helps you in your queries.

---

/* Global Variables */
Declare @maxlength int
Declare @Desc varchar(50)
Declare @property int
/* Global Cursors */
Declare pCursor cursor
for (select hmy from property where itype = 3 and isnull(binactive,0) = 0)
Open pCursor
Fetch Next FROM pCursor into @property
While @@fetch_status = 0
Begin
/* LOCAL Cursors */
Declare rCursor cursor LOCAL /* <-- Optional: Look at above comments */ For (Select distinct myAttribute2 from MyTable where property = @property) Open rCursor

Fetch Next From rCursor into @Desc
While @@fetch_status = 0
Begin
...
Fetch Next from rCursor into @Desc
END
Close rCursor
/* ALWAYS CLOSE BEFORE DEALLOCATION.
You still have to close with Declare Cursor Local */
Deallocate rCursor /* If you use LOCAL this line is not necessary */
Fetch Next From pCursor into @property
END
Close pCursor
Deallocate pCursor

---

As always use

Begin Tran
...
Rollback
Commit

in your code while testing.