Posted by Ben on 10/31/05 07:23

Hi, I have a table of folders that I would like to be able to display
in a depth first manner, similar to what you would see in Windows
Explorer. the table is defined similar to

CREATE TABLE Folders (
folderID int,
parentFolderID int,
name varchar(32)
)

.... I've left some things out of the definition for the simplicity of
example.

My desire is to quickly generate a record set containing the all child
folders of a specific folder along with the how many levels deep each
folder is, in a quick and memory efficient manner. I have done some
research through this group and found a few examples similar to what I
have hypothesized, however I was hoping for some feedback on what I
have decided to use.

Since this database table is already created and I cannot modify the
existing schema, it would not be very feasible to use a nested sets
model (unfortunately -- or if anyone has a suggestion?).

I plan on using a stored procedure that recusively calls itself.
something similar to the following:

/* This is assuming the table #TempFolderTable has
* already been declared globally
*/

sp_FolderDisplayRecurive @folder int, @level int AS
IF @@NESTLEVEL > 31 RETURN;
DECLARE @childFolder INT
DECLARE folders_cursor AS CURSOR LOCAL FOR
SELECT folderID FROM Folders
WHERE parentFolderID = @folderID
OPEN folders_cursor
FETCH NEXT FROM folders_cursorINTO @childFolder
while @@Fetch_status = 0
BEGIN
-- Insert current folder into
-- folder table (under parent @folder)
INSERT INTO #TempFolderTable
(FolderID, DepthLevel, ParentFolderID) VALUES
(@childFolder, @level, @folder)
-- Iterate over all of its children
EXEC sp_FolderDisplayRecursive
@childFolder, @level + 1

FETCH NEXT FROM folders_cursor INTO @childFolder
END
...


Will this incure a large overhead for a deep directory structure? Are
there any other problematic issues? Any suggestions and/or comments
are very welcome. Thanks!

[Back to original message]