меню1
меню2
.......подменю1
.......подменю2
..............подподменю1
.......подменю3
.......подменю4
меню4
меню5

CREATE PROCEDURE GetMenuTree 
(
    @TabID  int
)
AS

DECLARE @level int, @sublevel int
SET NOCOUNT ON

CREATE TABLE #outres 
(
	TabID 		int identity( 1 , 1 ) NOT NULL Primary Key,
	MsgId 		int NOT NULL Unique,
	ParentTabID	int NULL,
	LevelNum 	int NOT NULL,
	OrderByVal 	varchar( 255 ) NULL,
)

WHILE  1 = 1 

BEGIN
	SET @level=IsNull(@level, 0 )+ 1 
	
	INSERT INTO #outres (MsgId, ParentTabID, LevelNum)
	SELECT TabID, ParentTabID, @level
	FROM Tabs m
	WHERE (ParentTabID IN (SELECT MsgId FROM #outres) OR ParentTabID= 0 )
	AND TabID NOT IN (SELECT MsgId FROM #outres)
	ORDER BY TabOrder ASC 
	
	IF @@ROWCOUNT= 0  BREAK 
END

SET @sublevel= 1 

WHILE @sublevel<=@level

BEGIN
	UPDATE SELF SET OrderByVal=IsNull(parent.OrderByVal,'')+right('0000'+convert(varchar( 10 ),self.TabID), 5 )+'.'
	FROM #outres self
	LEFT JOIN #outres parent ON self.ParentTabID=parent.MsgId
	WHERE self.LevelNum=@sublevel
	
	SET @sublevel=@sublevel+ 1 
END

SELECT 
o.MsgId AS TabID, 
m.TabName, 
m.TabOrder, 
o.ParentTabID, 
o.LevelNum,
LTRIM(REPLICATE('---',o.LevelNum- 1 )+' '+m.TabName) AS ListTabName
FROM #outres o
INNER JOIN Tabs m ON o.MsgId=m.TabID
WHERE o.ParentTabID=@TabID OR o.ParentTabID= 0 
ORDER BY o.OrderByVal


GO