Solved

'Dynamic' cursors - tree walking

Posted on 2001-06-18
5
482 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 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 200 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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
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.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

920 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now