--测试表
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