http://topic.csdn.net/t/20051210/13/4450662.html
此存储过程改写测试可以正常使用，排序的字段要先排好序号，填写数据的时候，查询最大的序号再加1
然后使用该存储过程即可
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[tp_MoveOrder]
(
@ID int,
@Direction int -- @Direction : move up : -1 , move down : 1
)
AS
declare @TempID int
declare @OrderNo int
declare @MaxOrderNo int
declare @OrderNo_1 int
declare @OrderNo_2 int
declare @TempID_1 int
declare @TempID_2 int
begin
set NOCOUNT ON
begin
select @OrderNo=OrderNum,@TempID=ID from Ks_ScoreField where ID=@ID
select @MaxOrderNo=max(OrderNum) from Ks_ScoreField
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 @TempID_1=ID from Ks_ScoreField where OrderNum=@OrderNo_2
select @TempID_2=ID from Ks_ScoreField where OrderNum=@OrderNo_1
---------------------------------------------------------
--update orderno
update Ks_ScoreField set OrderNum=@OrderNo_1 where ID=@TempID_1
if @@ERROR <>0
RETURN 1
--update orderno
---------------------------------------------
UPDate Ks_ScoreField set ordernum=@OrderNo_2 where ID=@TempID_2
if @@ERROR <>0
RETURN 1
end
RETURN 0
end
==========================================
create proc p_move
@OrderNum int, --要处理的序号 就是排序的字段
@direction char(1)='下' --移动方向,'上'表示上移,'下'表示下移
as
declare @nid int
select @nid=case when @direction='上' then min(OrderNum) else max(OrderNum) end
from Ks_ScoreField
--如果不可移动,则退出
if @OrderNum=@nid return
--取得移动后的新 SortId 值
set @nid=@OrderNum+case when @direction='上' then -1 else @OrderNum end
--更新 SortId
update Ks_ScoreField set OrderNum=case when OrderNum=@OrderNum then @nid else @OrderNum end
where OrderNum in(@OrderNum,@nid)
======================================
要原理就是抓取当前记录的orderno，然后上移-1,下移+1,这样在数据库中已经把orderno字段给更新了，然后又是重新绑定，重新绑定的时候是按orderno来排序的，自然分页的顺序也就跟着变了，cs代码其实很简单就是单纯调用存储过程，所有逻辑已经在存储过程中处理过了。但是一定要记得在绑定的sql语句要有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