SQL数据列表移动排序存储过程

USE [aa]
GO
/****** Object:  StoredProcedure [dbo].[PR_Tables_Sort]    Script Date: 02/23/2011 21:59:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <li jing huan>
-- Create date: <2011-2-23>
-- Description: <通用列表排序操作>
-- =============================================
ALTER proc [dbo].[PR_Tables_Sort]
@TableName nvarchar(30),--表名
@SortFieldName nvarchar(30),--排序字段名
@IdFieldName nvarchar(20),--主键Id字段名
@sqlWhere nvarchar(300),--sql条件,不带where
@SortDirection nvarchar(6),--排序方向，即升序或降序(asc或desc)
@MoveDirection tinyint,--移动方向,0:向上移动1:向下移动
@CurentId int,--当前对应Id值
@returnValue int=-1 output--返回值(-1失败,-2已移至第一条数据,-3已移至最后一条数据)
as
declare @sql nvarchar(1200)
declare @sql2 nvarchar(1300)
declare @idxObj int=0
declare @idObj int=0
declare @sortValue int=0--排序值
declare @sortValue2 int=0--排序值
declare @RecordNum int=0--总记录数
set @sql=N'select '+@IdFieldName+','+@SortFieldName+' ,ROW_NUMBER() OVER (order by '+@SortFieldName+' '+@SortDirection+' ) as pos from '+@TableName
if(@sqlWhere is not null and @sqlWhere<>'' and LEN(@sqlWhere)>0)
set @sql+=N' where '+@sqlWhere
set @sql2=N'select @idxObj=t.pos,@sortValue=t.'+@SortFieldName+' from ('+@sql+') as t where '+@IdFieldName+'='+CONVERT(varchar,@CurentId)
exec sp_executesql @sql2,N'@idxObj int output,@sortValue int output',@idxObj output,@sortValue output
if(@MoveDirection=0)--上移
begin
if(@idxObj=1)
begin
set @returnValue=-2--已移至第一条数据
return @returnValue
end
else
begin
set @idxObj=@idxObj-1
set @sql2=N'select @idObj=t.'+@IdFieldName+',@sortValue2=t.'+@SortFieldName+' from ('+@sql+') as t where t.pos='+CONVERT(varchar,@idxObj)
exec sp_executesql @sql2,N'@idObj int output,@sortValue2 int output',@idObj output,@sortValue2 output
begin tran
set @sql2=N'update '+@TableName+' set '+@SortFieldName+'='+CONVERT(varchar,@sortValue)+' where '+@IdFieldName+'='+CONVERT(varchar,@idObj)
exec sp_executesql @sql2
set @sql2=N'update '+@TableName+' set '+@SortFieldName+'='+CONVERT(varchar,@sortValue2)+' where '+@IdFieldName+'='+CONVERT(varchar,@CurentId)
exec sp_executesql @sql2
if @@error <> 0 begin
rollback tran
set @returnValue=-1--失败
return @returnValue
end
commit tran
end
end
if(@MoveDirection=1)--下移
begin
set @sql2=N'select @RecordNum=count(*) from ('+@sql+') as t'
exec sp_executesql @sql2,N'@RecordNum int output',@RecordNum output
if(@idxObj=@RecordNum)
begin
set @returnValue=-3--已移至最后一条数据
return @returnValue
end
else
begin
set @idxObj=@idxObj+1
set @sql2=N'select @idObj=t.'+@IdFieldName+',@sortValue2=t.'+@SortFieldName+' from ('+@sql+') as t where t.pos='+CONVERT(varchar,@idxObj)
exec sp_executesql @sql2,N'@idObj int output,@sortValue2 int output',@idObj output,@sortValue2 output
begin tran
set @sql2=N'update '+@TableName+' set '+@SortFieldName+'='+CONVERT(varchar,@sortValue)+' where '+@IdFieldName+'='+CONVERT(varchar,@idObj)
exec sp_executesql @sql2
set @sql2=N'update '+@TableName+' set '+@SortFieldName+'='+CONVERT(varchar,@sortValue2)+' where '+@IdFieldName+'='+CONVERT(varchar,@CurentId)
exec sp_executesql @sql2
if @@error <> 0 begin
rollback tran
set @returnValue=-1--失败
return @returnValue
end
commit tran
end

end

PR_Tables_Sort.txt