通用的SQL數(shù)據(jù)上移，下移，置頂?shù)拇鎯^程
CREATE procedure [dbo].[UpdateTabelDataOrder]
@table varchar(50), --表名稱
@tableId varchar(50) , --表主鍵ID字段
@orderStr varchar(50), --排序字段名稱
@id varchar(20) , --數(shù)據(jù)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) --執(zhí)行內(nèi)部sql
set @tablename=@table
set @filtercolumn=@tableId
set @columnvalue=@id
set @countname=@orderStr
--第一步：處理動態(tài)表，列名，得到排序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;--使用輸出參數(shù)為變量@outPutValue賦值，變量后加關(guān)鍵字output
set @orderid = @outPutValue
--數(shù)據(jù)上移操作
if(@type = 'up')
begin
begin transaction
--第二步：處理第一條數(shù)據(jù)異常
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
--第三步：查詢操作數(shù)據(jù)ID的上一條數(shù)據(jù)ID
declare @board_Id int --和本數(shù)據(jù)相鄰的上一條OrderId
declare @orderboard_Id int --和本數(shù)據(jù)相鄰的上一條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
--第四步：對相關(guān)數(shù)據(jù)依次進行處理
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 --恢復(fù)@@trancount初始值，處理@@trancount bug
begin
commit transaction
end
end
--下移操作
if(@type = 'down')
begin
begin transaction
--第二步：處理第一條數(shù)據(jù)異常
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
--第三步：查詢操作數(shù)據(jù)ID的上一條數(shù)據(jù)ID
declare @board_Id2 int --和本數(shù)據(jù)相鄰的上一條OrderId
declare @orderboard_Id2 int --和本數(shù)據(jù)相鄰的上一條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
--第四步：對相關(guān)數(shù)據(jù)依次進行處理
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 --恢復(fù)@@trancount初始值，處理@@trancount bug
begin
commit transaction
end
end
--置頂操作
if(@type = 'top')
begin
begin transaction
--第二步：處理第一條數(shù)據(jù)異常
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
--第三步：查詢操作數(shù)據(jù)ID的上一條數(shù)據(jù)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 --恢復(fù)@@trancount初始值，處理@@trancount bug
begin
commit transaction
end
end