stored proc do action for every row in a resultset

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

stored proc do action for every row in a resultset

Author

Topic

webjagger
Starting Member

1 Post

Posted - 2003-05-13 : 04:48:21

Hello,
I want to resolve a hierarchy inside a sp. But I just want to get back the nodes of the hierarchy for a single user. And the user only has the lowest level nodes (~files) of the hierarchy, but not the 'folders'.
So files are nodes that are only children but never are parents (=never have child-elements),
folders are nodes that might be children but never are children

My idea was to give the sp the @hierarchyID and the @userID.
The sp selects all file-nodes from the user:
SELECT userFileNodeID
FROM tUserFileNodes
WHERE userID=@userID

Then the sp should take the userFileNodeIDs from the result set and fill them into the internal @userFileNodeIDs-parameter and then select the parentnode's ID of the current childnode:
SELECT DISTINCT parentNodeID
FROM tHierarchyStructure
WHERE childNodeID in @userFileNodeIDs
AND hierarchyID=@hierarchyID

Then again all the IDs of the result set have to be added to the internal @userFileNodeIDs-parameter do the select again and so on.

So at the end I should get all the file- and folder-nodes which I need to display the hierarchy tree for all nodes for a special user.

Does anyone have an idea how to get the result set items into the @userFileNodeIDs-parameter so that I can use it for the WHERE IN-clause?

Thanks in advance
Alex

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-05-13 : 07:32:28

Here's one way to do it, with iteration instead of recursion.

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=25964

I haven't played with recursive calls much yet in SQL ... My guess is a UDF is the way to go in this case.

A set-based language, to me, though, can't do recursion .... that implies an order to operations, doing things 1 row at a time. A set-based SELECT or UPDATE doesn't make too much sense with recursion. Other than simple functions, of course, like a recursive in-line UDF that reverses a string or something like that.

SQL generally evaluates things from the "in-side out", while recursion is more constantly moving in and out.

Sorry, hard to put this into words but I hope it makes some sense!

- Jeff

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-05-13 : 07:53:58

By the way -- in the link I gave, you would do the OPPOSITE of what I demonstrated. YOu know which child nodes you want, you would then work your way UP to the eventual root through the children. The Level field that I used then would be levels ABOVE the child that each parent is.

Let me know if you would like me to rewrite this function for you. ALso, it sounds like you might want to pass a CSV to this function to indicate which children to display? Search the site for info on that.

- Jeff

Edited by - jsmith8858 on 05/13/2003 07:54:30

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources