所有逻辑都写到了存储过程中,所以在使用的时候,直接调用存储过程即可,无需在前台代码中做过多的处理,但是一定要记得在绑定的sql语句中必须有order by orderno来排序
--
测试表
if exists ( select * from dbo.sysobjects where id = object_id (N ' [dbo].[Job_Person] ' ) and OBJECTPROPERTY (id, N ' IsUserTable ' ) = 1 )
drop table [ dbo ] . [ Job_Person ]
GO
CREATE TABLE [ dbo ] . [ Job_Person ] (
[ Job_PersonID ] [ int ] NOT NULL ,
[ PersonID ] [ int ] NOT NULL ,
[ OrderNo ] [ int ] NULL ,
[ CreateOn ] [ datetime ] NULL ,
[ CreateByID ] [ int ] NULL ,
[ ModifyOn ] [ datetime ] NULL ,
[ ModifyByID ] [ int ] NULL
) ON [ PRIMARY ]
GO
-- 测试存储过程
CREATE PROCEDURE tp_MoveOrder_Entity_Job
(
@Job_EntityID int , -- 表主键id
@Direction INT , -- @Direction : move up : -1 , move down : 1
@UserID int -- 修改人id
)
AS
declare @EntityID int
declare @OrderNo INT
declare @MaxOrderNo Int
declare @OrderNo_1 int
declare @OrderNo_2 int
declare @Job_EntityID_1 int
declare @Job_EntityID_2 int
BEGIN
SET NOCOUNT ON
BEGIN
Select @OrderNo = OrderNo, @EntityID = PersonID From Job_Person
Where Job_PersonID = @Job_EntityID
select @MaxOrderNo = max (OrderNo) From Job_Person
WHERE PersonID = @EntityID
SET @OrderNo_1 = @OrderNo
SET @OrderNo_2 = @OrderNo + @Direction
if ( @MaxOrderNo = 1 )
return 0 -- 只有一条记录则返回
if (( @OrderNo = 1 ) AND ( @Direction = - 1 )) -- 设置move up orderno
BEGIN
SET @OrderNo_1 = 1
SET @OrderNo_2 = @MaxOrderNo
END
if (( @OrderNo = @MaxOrderNo ) AND ( @Direction = 1 )) -- 设置move down orderno
BEGIN
SET @OrderNo_1 = 1
SET @OrderNo_2 = @MaxOrderNo
END
-- 根据orderno取得不同的move资料
select @Job_EntityID_1 = Job_PersonID from Job_Person
where PersonID = @EntityID and OrderNo = @OrderNo_2
select @Job_EntityID_2 = Job_PersonID from Job_Person
where PersonID = @EntityID and OrderNo = @OrderNo_1
-- update orderno
update Job_Person
set orderno = @OrderNo_1 ,
ModifyOn = getdate (),
ModifyByID = @UserID
where Job_PersonID = @Job_EntityID_1
IF @@ERROR <> 0
RETURN 1
-- update orderno
update Job_Person
set orderno = @OrderNo_2 ,
ModifyOn = getdate (),
ModifyByID = @UserID
where Job_PersonID = @Job_EntityID_2
IF @@ERROR <> 0
RETURN 1
END
RETURN 0
END
GO
if exists ( select * from dbo.sysobjects where id = object_id (N ' [dbo].[Job_Person] ' ) and OBJECTPROPERTY (id, N ' IsUserTable ' ) = 1 )
drop table [ dbo ] . [ Job_Person ]
GO
CREATE TABLE [ dbo ] . [ Job_Person ] (
[ Job_PersonID ] [ int ] NOT NULL ,
[ PersonID ] [ int ] NOT NULL ,
[ OrderNo ] [ int ] NULL ,
[ CreateOn ] [ datetime ] NULL ,
[ CreateByID ] [ int ] NULL ,
[ ModifyOn ] [ datetime ] NULL ,
[ ModifyByID ] [ int ] NULL
) ON [ PRIMARY ]
GO
-- 测试存储过程
CREATE PROCEDURE tp_MoveOrder_Entity_Job
(
@Job_EntityID int , -- 表主键id
@Direction INT , -- @Direction : move up : -1 , move down : 1
@UserID int -- 修改人id
)
AS
declare @EntityID int
declare @OrderNo INT
declare @MaxOrderNo Int
declare @OrderNo_1 int
declare @OrderNo_2 int
declare @Job_EntityID_1 int
declare @Job_EntityID_2 int
BEGIN
SET NOCOUNT ON
BEGIN
Select @OrderNo = OrderNo, @EntityID = PersonID From Job_Person
Where Job_PersonID = @Job_EntityID
select @MaxOrderNo = max (OrderNo) From Job_Person
WHERE PersonID = @EntityID
SET @OrderNo_1 = @OrderNo
SET @OrderNo_2 = @OrderNo + @Direction
if ( @MaxOrderNo = 1 )
return 0 -- 只有一条记录则返回
if (( @OrderNo = 1 ) AND ( @Direction = - 1 )) -- 设置move up orderno
BEGIN
SET @OrderNo_1 = 1
SET @OrderNo_2 = @MaxOrderNo
END
if (( @OrderNo = @MaxOrderNo ) AND ( @Direction = 1 )) -- 设置move down orderno
BEGIN
SET @OrderNo_1 = 1
SET @OrderNo_2 = @MaxOrderNo
END
-- 根据orderno取得不同的move资料
select @Job_EntityID_1 = Job_PersonID from Job_Person
where PersonID = @EntityID and OrderNo = @OrderNo_2
select @Job_EntityID_2 = Job_PersonID from Job_Person
where PersonID = @EntityID and OrderNo = @OrderNo_1
-- update orderno
update Job_Person
set orderno = @OrderNo_1 ,
ModifyOn = getdate (),
ModifyByID = @UserID
where Job_PersonID = @Job_EntityID_1
IF @@ERROR <> 0
RETURN 1
-- update orderno
update Job_Person
set orderno = @OrderNo_2 ,
ModifyOn = getdate (),
ModifyByID = @UserID
where Job_PersonID = @Job_EntityID_2
IF @@ERROR <> 0
RETURN 1
END
RETURN 0
END
GO