/********************************************************************************************
* *
* 存储过程名称: Functions_AutoUpdate *
* 存储过程功能: 维护树资料 *
* 创建标识: sunlq20070824 *
* *
* 入口参数:
@iFunctionId bigint, 类别id
@iOrder bigint 排序号
@iNewOrder int out --新排序号
* 出口参数: 0 *
********************************************************************************************/
if EXISTS (select * from dbo.sysobjects where id = object_id(N'[dbo].[Functions_AutoUpdate]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP procedure [dbo].[Functions_AutoUpdate]
GO
CREATE PROCEDURE Functions_AutoUpdate
@iFunctionId bigint, --id
@iOrder int, --排序号
@iNewOrder int out --新排序号
AS
DECLARE @iLevel bigint,@cParentStr varchar(1000),@iParendId bigint,@iSubCount int,@iChildFunctionId bigint
--1获得ParentStr
SELECT @iParendId= ParentID FROM S_Functions WHERE FunctionId=@iFunctionId
IF @iParendId = -1
BEGIN
SET @cParentStr = convert(varchar,@iFunctionId)
SET @iLevel= 1
END
ELSE
BEGIN
SELECT @cParentStr=ParentStr+','+convert(varchar,@iFunctionId), @iLevel=Levels+1 FROM S_Functions WHERE FunctionId=@iParendId
END
--2.获得子类个数
SELECT @iSubCount=COUNT(*) FROM S_Functions WHERE ParentID=@iFunctionId
IF @@Error != 0 GOTO ERROR_RETURN --捕获错误
--3 更新本id资料
UPDATE S_Functions SET ParentStr=@cParentStr, Levels=@iLevel,SubCount= @iSubCount,OrderNum=@iOrder WHERE FunctionId=@iFunctionId
IF @@Error != 0 GOTO ERROR_RETURN --捕获错误
--4设置新Order
SET @iNewOrder=@iOrder+1
--5如果有子级目录则循环下去
IF EXISTS(SELECT * FROM [S_Functions] WHERE [ParentId]=@iFunctionId)
BEGIN
DECLARE op_cursor CURSOR LOCAL FOR SELECT FunctionId From [S_Functions] WHERE [ParentId]=@iFunctionId
OPEN op_cursor
FETCH NEXT FROM op_cursor into @iChildFunctionId
WHILE @@FETCH_STATUS = 0
BEGIN
exec Functions_AutoUpdate @iChildFunctionId,@iNewOrder,@iNewOrder output
FETCH NEXT FROM op_cursor into @iChildFunctionId
END
CLOSE op_cursor
DEALLOCATE op_cursor
END
ERROR_RETURN:
return 0
GO