Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 491
  • Last Modified:

'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
0
meowsh
Asked:
meowsh
  • 3
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I assume that ref uses the names, if not, you should adjust the below code to use the ID instead of the name...

DECLARE @varname VARCHAR(100)

SET @varname = 'Bottom'

WHILE NOT (@varname IS NULL)
BEGIN
  -- Do your DML for the @varname
  <code goes here>
  -- find the parent
  SELECT @varname = parentID
  FROM ref
  WHERE [NAME] = @varname
END



Cheers
0
 
nigelrivettCommented:
I would never use a cursor.
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.
0
 
nigelrivettCommented:
Or one variable like angelIII :-).
0
 
meowshAuthor Commented:
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)
0
 
nigelrivettCommented:
Don't believe everything you hear from MS - sometimes they can be more efficient by enabling you to split queries into smaller parts.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now