Solved

'Dynamic' cursors - tree walking

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

832 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