'Dynamic' cursors - tree walking
Posted on 2001-06-18
Please help out a weary sole who is troubled by a presumably simple T-SQL question.
I have a t_sql program that is effectively trying to walk up and down a data tree.
I have a table called ref data with each row, bar the top 'parent row' having, amongst other attributes, a parent ref data attribute.
For example I may have rows like
ID Name Other Attribute(s) ParentID
2 Middle Top
3 Bottom Middle
I am trying to write a proc that finds a row in the table, determines if it has a parent, does some DML, ....and then if the row has a parent, repeat the loop (find row, get parent, do DML)...looping until you get to the top if the tree.
The way I have tried (unsuccessfully) is to use cursors.
1. I have an outer cursor which get every row in the table trapping the ParentID in a variable (varID).
2. Do DML
3. Open inner cursor which has clause 'WHERE ID=varID'
4. Do DML
5. Overwrite the varID used in 3 with the parentID returned in 3
6. Fetch the next row in inner cursor hoping that the inner cursor will re-execute with the new varID thus walking the tree....
But it doesnt re-execute the cursor and thus isnt walking the tree.
Am I approaching this correctly? Is there a better way.. Whats the syntax?
Top points paid to he who helps me the best