看看老师给的存储过程把

最新推荐文章于 2021-04-08 14:37:51 发布

yvi312082007

最新推荐文章于 2021-04-08 14:37:51 发布

阅读量825

点赞数

文章标签：存储脚本

本文链接：https://blog.csdn.net/yvi312082007/article/details/7028960

版权

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