CREATE PROCEDURE expand (@categoryID smallint) as -- где categoryID указывает с какой ветки или сразу с корня разворачивать дерево
SET NOCOUNT ON
DECLARE @level int, @line char(20), @razd varchar(40)
CREATE TABLE #stack (categoryID smallint, _level int)
CREATE TABLE #catalog (categoryID smallint, title varchar(40), _level int)
INSERT INTO #stack VALUES (@categoryID, 1)
SELECT @level = 1


WHILE @level > 0
BEGIN
IF EXISTS (SELECT * FROM #stack WHERE _level = @level)
BEGIN
SELECT @categoryID = categoryID
FROM #stack
WHERE _level = @level
SELECT @razd = title FROM categories WHERE categoryID = @categoryID
IF (@level !=1) INSERT #catalog (categoryID, title, _level) VALUES (@categoryID, @razd, @level)
DELETE FROM #stack
WHERE _level = @level
AND categoryID = @categoryID
INSERT #stack
SELECT categoryID, @level + 1
FROM categories
WHERE parentID = @categoryID AND parentID IS NOT NULL ORDER BY title DESC
IF @@ROWCOUNT > 0
SELECT @level = @level + 1
END
ELSE
SELECT @level = @level - 1
END -- WHILE

DROP TABLE #stack
SELECT categoryID, title, _level FROM #catalog
DROP TABLE #catalog