meowsh
asked on
'Dynamic' cursors - tree walking
Hi Experts.
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
1 Top
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
Thanks all
Meowsh
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
1 Top
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
Thanks all
Meowsh
ASKER CERTIFIED SOLUTION
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Or one variable like angelIII :-).
ASKER
Works a treat...thanks angellll
I deliberately didnt want to use a temp table as whilst they make programming a lot easier they are very processed by SQLServer very inefficiently (my company is a MS certified solution provider and our 'support' team at MS said to avoid them if you want procs to perform well against large data volumes)
I deliberately didnt want to use a temp table as whilst they make programming a lot easier they are very processed by SQLServer very inefficiently (my company is a MS certified solution provider and our 'support' team at MS said to avoid them if you want procs to perform well against large data volumes)
Don't believe everything you hear from MS - sometimes they can be more efficient by enabling you to split queries into smaller parts.
Better to create a temp table with all the data you have to execute.
in fact as you are only accessing one at a time you should be able to do this with a couple of variables and a loop.