?
Solved

'Dynamic' cursors - tree walking

Posted on 2001-06-18
5
Medium Priority
?
492 Views
Last Modified: 2012-05-04
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
Comment
Question by:meowsh
  • 3
5 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 800 total points
ID: 6203307
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
 
LVL 18

Expert Comment

by:nigelrivett
ID: 6203308
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
 
LVL 18

Expert Comment

by:nigelrivett
ID: 6204175
Or one variable like angelIII :-).
0
 
LVL 3

Author Comment

by:meowsh
ID: 6207141
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
 
LVL 18

Expert Comment

by:nigelrivett
ID: 6207497
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: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

585 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question