改变顺序存储过程

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER procedure [dbo].[Proc_PicOrderMove]
   @SID int,
   @type varchar(50)
as
begin
   declare @NowPicOrder int;
declare @PrevSID int;
declare @NextSID int;
declare @PrevPicOrder int;
declare @NextPicOrder int;

select @NowPicOrder=PicOrder from SliderPic where sid=@SID;
if(@type='prev')
begin
   select top 1 @PrevPicOrder=PicOrder,@PrevSID=SID from SliderPic where PicOrder<@NowPicOrder order by PicOrder desc;
   if(@prevSID is not null)
begin
   update SliderPic set PicOrder=@PrevPicOrder where SID=@SID;
   update SliderPic set PicOrder=@NowPicOrder where SID=@PrevSID;
end;
end
else
begin
  select top 1 @NextPicOrder=PicOrder,@NextSID=SID from SliderPic where PicOrder>@NowPicOrder order by PicOrder asc;
  if(@NextSID is not null)
begin
   update SliderPic set PicOrder=@NextPicOrder where SID=@SID;
   update SliderPic set PicOrder=@NowPicOrder where SID=@NextSID;
end;
end
end

SliderPic--前台首页滑动图片

字段	类型	描述
SID	Identity(1,1)	ID
SRC	Text	图片地址
PicOrder	Int	顺序
InsertDate	Datetime	添加时间

 

 实际上，是把他的PicOrder和上一个下一个互换。