通用的SQL数据上移，下移，置顶的存储过程
CREATE procedure [dbo].[UpdateTabelDataOrder]
@table varchar(50), --表名称
@tableId varchar(50) , --表主键ID字段
@orderStr varchar(50), --排序字段名称
@id varchar(20) , --数据ID
@type varchar(20) --移动类型
as
declare @orderid varchar(50), --排序ID
@outPutValue int, --输出值
@tablename nvarchar(50), --表名称
@filtercolumn nvarchar(50), --条件字段
@columnvalue nvarchar(50), --主键id值
@countname nvarchar(50), --查询的字段,排序字段
@sql nvarchar(max) --执行内部sql
set @tablename=@table
set @filtercolumn=@tableId
set @columnvalue=@id
set @countname=@orderStr
--第一步：处理动态表，列名，得到排序ID
set @sql='select @outValue='+@countname +' from ' +quotename(@tablename) +' where '
exec sp_executesql
@sql,
as nvarchar(50),@outValue as int output',
@cv=@columnvalue,
@outValue=@outPutValue output;--使用输出参数为变量@outPutValue赋值，变量后加关键字output
set @orderid = @outPutValue
--数据上移操作
if(@type = 'up')
begin
begin transaction
--第二步：处理第一条数据异常
declare @countNum int , @sql_2 nvarchar(max)
set @sql_2='select @outValue=count(*) from ' +quotename(@tablename) +' where < ' + @orderid
exec sp_executesql
@sql_2,
as int output',
@outValue=@outPutValue output
set @countNum = @outPutValue
if(@countNum) >0
begin
--第三步：查询操作数据ID的上一条数据ID
declare @board_Id int --和本数据相邻的上一条OrderId
declare @orderboard_Id int --和本数据相邻的上一条OrderboardId
declare @sql_3 nvarchar(max)
set @sql_3='select top 1 @outValue='+@filtercolumn +' from ' +quotename(@tablename)
+' where in (select from '+quotename(@tablename)
+' where < ) order by desc'
exec sp_executesql
@sql_3,
as int output',
@outValue=@outPutValue output
set @board_Id = @outPutValue
set @sql_3='select top 1 @outValue='+@countname +' from ' +quotename(@tablename)
+' where in (select from '+quotename(@tablename)
+' where < ) order by desc'
exec sp_executesql
@sql_3,
as int output',
@outValue=@outPutValue output
set @orderboard_Id = @outPutValue
--第四步：对相关数据依次进行处理
declare @sql_4 nvarchar(max)
set @sql_4='update '+quotename(@tablename)+' set = @zdzhi where = @id'
exec sp_executesql
@sql_4,
as int ,@id as varchar(50)',
@zdzhi = @orderboard_Id,
@id=@columnvalue
set @sql_4='update '+quotename(@tablename)+' set = @zdzhi where = @upid'
exec sp_executesql
@sql_4,
as int,@upid as int',
@zdzhi = @orderid,
@upid = @board_Id
if @@error<>0
begin
rollback tran
end
else
begin
commit transaction
end
end
else --恢复@@trancount初始值，处理@@trancount bug
begin
commit transaction
end
end
--下移操作
if(@type = 'down')
begin
begin transaction
--第二步：处理第一条数据异常
declare @countNum2 int , @sql_7 nvarchar(max)
set @sql_7='select @outValue=count(*) from ' +quotename(@tablename) +' where > ' + @orderid
exec sp_executesql
@sql_7,
as int output',
@outValue=@outPutValue output
set @countNum2 = @outPutValue
if(@countNum2) >0
begin
--第三步：查询操作数据ID的上一条数据ID
declare @board_Id2 int --和本数据相邻的上一条OrderId
declare @orderboard_Id2 int --和本数据相邻的上一条OrderboardId
declare @sql_5 nvarchar(max)
set @sql_5='select top 1 @outValue='+@filtercolumn +' from ' +quotename(@tablename)
+' where in (select from '+quotename(@tablename)
+' where > ) order by '
exec sp_executesql
@sql_5,
as int output',
@outValue=@outPutValue output
set @board_Id2 = @outPutValue
set @sql_5='select top 1 @outValue='+@countname +' from ' +quotename(@tablename)
+' where in (select from '+quotename(@tablename)
+' where > ) order by '
exec sp_executesql
@sql_5,
as int output',
@outValue=@outPutValue output
set @orderboard_Id2 = @outPutValue
--第四步：对相关数据依次进行处理
declare @sql_6 nvarchar(max)
set @sql_6='update '+quotename(@tablename)+' set = @zdzhi where = @id'
exec sp_executesql
@sql_6,
as int ,@id as varchar(50)',
@zdzhi = @orderboard_Id2,
@id=@columnvalue
set @sql_6='update '+quotename(@tablename)+' set = @zdzhi where = @upid'
exec sp_executesql
@sql_6,
as int,@upid as int',
@zdzhi = @orderid,
@upid = @board_Id2
if @@error<>0
begin
rollback tran
end
else
begin
commit transaction
end
end
else --恢复@@trancount初始值，处理@@trancount bug
begin
commit transaction
end
end
--置顶操作
if(@type = 'top')
begin
begin transaction
--第二步：处理第一条数据异常
declare @countNum3 int , @sql_8 nvarchar(max)
set @sql_8='select @outValue=count(*) from ' +quotename(@tablename) +' where < ' + @orderid
exec sp_executesql
@sql_8,
as int output',
@outValue=@outPutValue output
set @countNum3 = @outPutValue
if(@countNum3) >0
begin
--第三步：查询操作数据ID的上一条数据ID
set @sql_8='update '+quotename(@tablename)+' set = +1 where
+' in (select from ' +quotename(@tablename) +' where in '
+'(select from ' +quotename(@tablename) +' where < ))'
print @sql_8
exec sp_executesql
@sql_8
set @sql_8='update '+quotename(@tablename)+' set = 1 where = @upid'
exec sp_executesql
@sql_8,
as int',
@upid = @columnvalue
if @@error<>0
begin
rollback tran
end
else
begin
commit transaction
end
end
else --恢复@@trancount初始值，处理@@trancount bug
begin
commit transaction
end
end
更多信息请查看IT技术专栏
