Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

'Dynamic' cursors - tree walking

Posted on 2001-06-18
5
Medium Priority
?
490 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

972 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