|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2001-01-05 : 15:15:49
|
Wilfred writes "Hi There!
I sincerely hope you can give me some ideas on this one. I am trying to generate a tree structure from a database table (Folders) that has a circular reference to itself. The fields in it are an Id, Name and Parent_Folder. I am trying to generate the correct sequence of nodes using two SProcs (stored procedures), and Cursors to accomplish this. The second sproc is giving me trouble, in that, it gives me a cursor does not exist if I close and deallocate it before the recursive call, and a cursor already exists if I do not close it. So, both ways, I'm in a fix. Here's what I have so far. Any ideas on how to overcome this (or if it could be done differently), would be most certainly appreciated. CREATE Procedure sp_getTreeNodes
@NodeId int
As
DECLARE @intFolder int, @chrFolderName Char(50), @intChildCount int, @intParentFolder int
DECLARE TreeCursor Cursor
FOR
/* Get Root nodes */
SELECT A.Folder_Id, A.Folder_Name, COUNT(B.Parent_Folder) AS nbrOfChildren, A.Parent_Folder
FROM Folders A, Folders B
WHERE A.Parent_Folder = @NodeId AND A.Folder_Id <> 0 AND A.Folder_Id *= B.Parent_Folder
GROUP BY A.Folder_Id, A.Folder_Name, B.Parent_Folder, A.Parent_Folder
ORDER BY A.Folder_Id
/* Flush out the output table */
DELETE tblTree
OPEN TreeCursor
FETCH NEXT FROM TreeCursor INTO @intFolder, @chrFolderName, @intChildCount, @intParentFolder
WHILE (@@FETCH_STATUS=0)
BEGIN
/* Call the Recursive SProc */
EXEC sp_getChildNodes @intFolder, @chrFolderName, @intChildCount, @intParentFolder
FETCH NEXT FROM TreeCursor INTO @intFolder, @chrFolderName, @intChildCount, @intParentFolder
END
CLOSE TreeCursor
DEALLOCATE TreeCursor
print 'Procedure successfully Executed.....'
/* ---------------------------------------------------- */
CREATE Procedure sp_getChildNodes
@intFolder int, @chrFolderName char(50), @intChildCount int, @intParentFolder int
As
/* Write to the output table */
INSERT INTO tblTree VALUES (@intFolder, @chrFolderName, @intChildCount, @intParentFolder)
DECLARE TreeCursor1 Cursor
FOR
/* Get all subfolders for the given folder */
SELECT A.Folder_Id, A.Folder_Name, COUNT(B.Parent_Folder) AS nbrOfChildren, A.Parent_Folder
FROM Folders A, Folders B
WHERE A.Parent_Folder = @intFolder AND A.Folder_Id <> 0 AND A.Folder_Id *= B.Parent_Folder
GROUP BY A.Folder_Id, A.Folder_Name, B.Parent_Folder, A.Parent_Folder
ORDER BY A.Folder_Id
OPEN TreeCursor1
FETCH NEXT FROM TreeCursor1 INTO @intFolder, @chrFolderName, @intChildCount, @intParentFolder
WHILE (@@FETCH_STATUS=0)
BEGIN
If (@intChildCount > 0)
Begin
/* This is where I'm running into problems */
/* CLOSE TreeCursor1
DEALLOCATE TreeCursor1 */
/* Calls itself again with the current Folder */
EXEC sp_getChildNodes @intFolder, @chrFolderName, @intChildCount, @intParentFolder
End
FETCH NEXT FROM TreeCursor1 INTO @intFolder, @chrFolderName, @intChildCount, @intParentFolder
END
CLOSE TreeCursor1
DEALLOCATE TreeCursor1
print 'Procedure - 1 successfully Executed.....'
Thanks,
Wilfred." |
|
Recursive Calls wih SProcs