Querying hierarchical folder information for all rows in a table

Question

We have an old database application with a table that represents folder structure. My use case is very similar to the one in this old post, except I need to be able to return the folder path information for all rows in my table, which is fairly small (727 records).

The function given in that post works and I've tried to reverse-engineer it to return results for all rows, but I'm clearly in over my head as I get no results.

The code I'm trying is below. And yes, just to confirm, the back-end is SQL 2000. The application has been neglected for a while but we are now finally trying to retire it, which is why I'm trying to get this folder structure information. All help is appreciated. Thanks.

USE MyDB
GO
DROP TABLE #FolderTemp

DECLARE @Counter INT
DECLARE @MaxId INT
DECLARE @FolderID INT
DECLARE @ParentID INT
DECLARE @FolderPath varchar(1000)
DECLARE @FolderName varchar(1000)

SELECT MYFOLDER_Id
    , MYFOLDER_ParentId
    , MYFOLDER_LogicalName 
INTO #FolderTemp FROM MYTABLE

Select @Counter = min(MYFOLDER_Id), @MaxId = max(MYFOLDER_Id)
FROM #FolderTemp
GROUP BY MYFOLDER_LogicalName

WHILE (@Counter IS NOT NULL
    AND @Counter <= @MaxId)
BEGIN


SELECT MYFOLDER_ParentID = @ParentID, MYFOLDER_LogicalName = @FolderName, @FolderPath
      FROM #FolderTemp
      WHERE MYFOLDER_Id = @FolderID
      ((
    select @FolderPath = MYFOLDER_LogicalName + '\' + @FolderPath, @ParentID = MYFOLDER_ParentID
           from #FolderTemp
           where MYFOLDER_Id = @ParentID
    ))
PRINT @FolderPath + '\' + @FolderName
    SET @Counter  = @Counter  + 1        
END

Answer 1

Your while loop appears to increment a variable called @counter but the query inside the while uses a variable called @folderid which is never assigned to and will always have its default value of null

Recursive queries are hard work in sqlserver 2000. If this is a one time op to get the data out, you could do worse than just copypasting as many left joins as you need for the deepest path eg

SELECT t0.name, t1.name, t2.name ...
FROM t t0
  LEFT JOIN t t1 ON t1.parentid = t0.id 
  LEFT JOIN t t2 ON t2.parentid = t1.id 
...