使用三层架构完成菜单项移动操作存储过程

 

USE student
GO
/****** 对象:  StoredProcedure [dbo].[sp_MenuTree_Sort]    脚本日期: 11/29/2011 20:37:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create proc [dbo].[sp_MenuTree_Sort]
(
 @MenuID VARCHAR(20),
 @SortDir BIT  )
AS
declare @ParentID varchar(20)--父节点的ID
declare @MenuID2 varchar(20) --存放另一条记录的MenuID，要交换menuID菜单项的ID
declare @MenuOrder1 int
declare @MenuOrder2 int

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