Posted by Erland Sommarskog on 11/01/05 00:43

Ben (vanevery@gmail.com) writes:
> 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.
>...
> 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

First, don't use sp_ for your procedure names. This prefix is reserved
for system stored procedure, and SQL Server first looks in master for
these.

Then, there is no need for neither recursive procedure nor cursor. The
example illustates:



CREATE TABLE Folders (
folderID int NOT NULL PRIMARY KEY,
parentFolderID int NULL REFERENCES Folders(folderID),
name varchar(32) NOT NULL
)
go
INSERT Folders (folderID, parentFolderID, name)
SELECT 1, NULL, 'C:\'
UNION
SELECT 2, 1, 'Windows'
UNION
SELECT 3, 1, 'Program Files'
UNION
SELECT 4, 2, 'System 32'
UNION
SELECT 5, 4, 'Drivers'
go
CREATE TABLE #tmp (
folderID int NOT NULL,
parentFolderID int NULL,
lvl tinyint NOT NULL
)
go
DECLARE @level tinyint
SELECT @level = 1
INSERT #tmp(folderID, parentFolderID, lvl)
SELECT folderID, NULL, 1
FROM Folders
WHERE parentFolderID IS NULL
WHILE 1 = 1
BEGIN
SELECT @level = @level + 1
INSERT #tmp(folderID, parentFolderID, lvl)
SELECT a.folderID, a.parentFolderID, @level
FROM Folders a
WHERE EXISTS (SELECT *
FROM #tmp t
WHERE t.folderID = a.parentFolderID
AND t.lvl = @level - 1)
IF @@rowcount = 0
BREAK
END

SELECT * FROM #tmp
go
DROP TABLE #tmp
DROP TABLE Folders

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Back to original message]