Calling:
-----------------------------------------
USE [db02_MelaEmp]
GO

DECLARE	@return_value int

EXEC	@return_value = [dbo].[SelectMenus1]
		@psSystemID = N'MELAEMP',
		@piParent = -1

SELECT	'Return Value' = @return_value

GO


Code:
---------------------------------------
USE [db02_MelaEmp]
GO
/****** Object:  StoredProcedure [dbo].[SelectMenus1]    Script Date: 03/05/2007 15:56:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[SelectMenus1] (@psSystemID char(20), @piParent int) AS
-- 
	SET NOCOUNT ON
	DECLARE @iLevel int, @sLine char(50)
    DECLARE @i1 int
    DECLARE @s1 char(30)

	-- Reset FLevel
	update TPrograms
	set FLevel = -1
	where FSystemID = @psSystemID

		CREATE TABLE #stack (FItem int, FLevel int)
		INSERT INTO #stack VALUES (@piParent, 1)
		SELECT @iLevel = 1  
		WHILE @iLevel > 0
		BEGIN
			IF EXISTS (SELECT * FROM #stack WHERE FLevel = @iLevel)
			BEGIN
				SELECT @piParent = FItem
					FROM #stack
					WHERE FLevel = @iLevel
				
				SELECT @i1 = @iLevel - 1
				SELECT @sLine = cast(@i1 as char(4)) + space(@i1) + cast(@piParent as char(10))
				PRINT @sLine

				-- Change Level, root = 0
				update TPrograms
				set FLevel = @i1
				where FParentNo = @piParent
				and FSystemID = @psSystemID

				DELETE FROM #stack WHERE FLevel = @iLevel AND FItem = @piParent
				INSERT #stack
					SELECT FProgramNo, @iLevel + 1
					FROM TPrograms
					WHERE FParentNo = @piParent
					and FSystemID = @psSystemID
					ORDER BY FSequence desc, FProgramNo desc
				IF @@ROWCOUNT > 0
					SELECT @iLevel = @iLevel + 1
            END
			ELSE
				SELECT @iLevel = @iLevel - 1
		END -- WHILE

	-- Original Level and sequence
	select *
		from TPrograms
		where FSystemID = @psSystemID
		order by FParentNo, FSequence
	

Result in messages
---------------------------------
0   -1                                            
1    10                                           
2     1010                                        
2     1020                                        
2     1030                                        
1    20                                           
2     2010                                        
2     2020                                        
2     2030                                        
1    30                                           
1    40                                           
1    900                                          
1    910                                          
1    920                                          

(1 row(s) affected)