存储过程排序

 

USE student
GO
/****** 对象:  StoredProcedure [dbo].[sp_MenuTree_Sort]    脚本日期: 11/29/2011 20:37:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[sp_MenuTree_Sort]
(
-- @SiteID VARCHAR(50),
 @MenuID VARCHAR(20),
 @SortDir BIT
)
AS
declare @ParentID varchar(20)
declare @MenuID2 varchar(20)
declare @MenuOrder1 int
declare @MenuOrder2 int

select @ParentID = ParentID,@MenuOrder1 = MenuOrder from tb_MenuTree where MenuID = @MenuID

if @SortDir = 1
begin
 select top 1 @MenuID2 = MenuID,@MenuOrder2 = MenuOrder from tb_MenuTree where MenuOrder < @MenuOrder1 and ParentID = @ParentID order by MenuOrder desc
end
else
begin
 select top 1 @MenuID2 = MenuID,@MenuOrder2 = MenuOrder from tb_MenuTree where MenuOrder > @MenuOrder1 and ParentID = @ParentID order by MenuOrder asc
end

begin tran
 update tb_MenuTree set MenuOrder = @MenuOrder1 where MenuID = @MenuID2
 update tb_MenuTree set MenuOrder = @MenuOrder2 where MenuID = @MenuID
if @@ERROR = 0
COMMIT TRAN
ELSE
ROLLBACK TRAN