存储过程:
use student
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE sp_MenuTree_Sort
(
@MenuID VARCHAR(20),
@SortDir BIT --1--up;0--down
)
AS
declare @ParentID varchar(20) --父节点id
declare @MenuID2 VARCHAR(20) --要交换menuorder菜单项的id
declare @MenuOrder1 int
declare @MenuOrder2 int
BEGIN
SELECT @ParentID=ParentID,@MenuOrder1 = MenuOrder froM MenuTree where MenuID = @MenuID
IF @SortDir=1 --向上移动
begin
select top 1 @MenuID2=MenuID,@MenuOrder2=menuorder from MenuTree where MenuOrder < @MenuOrder1 and ParentID=@ParentID
order by MenuOrder desc
END
else --向下移动
begin select top 1 @MenuID2=MenuID,@MenuOrder2=menuorder from MenuTree where MenuOrder>@MenuOrder1 and ParentID=@ParentID
order by MenuOrder asc
end
begin TRAN
update MenuTree set MenuOrder = @MenuOrder1 where MenuID = @MenuID2
update MenuTree set MenuOrder = @MenuOrder2 where MenuID = @MenuID
if @@ERROR = 0
COMMIT TRAN
ELSE
ROLLBACK TRAN
end