通用的SQL數(shù)據(jù)上移，下移，置頂?shù)拇鎯?chǔ)過(guò)程

CREATE procedure [dbo].[UpdateTabelDataOrder]

@table varchar(50), --表名稱(chēng)

@tableId varchar(50) , --表主鍵ID字段

@orderStr varchar(50), --排序字段名稱(chēng)

@id varchar(20) , --數(shù)據(jù)ID

@type varchar(20) --移動(dòng)類(lèi)型

as

declare @orderid varchar(50), --排序ID

@outPutValue int, --輸出值

@tablename nvarchar(50), --表名稱(chēng)

@filtercolumn nvarchar(50), --條件字段

@columnvalue nvarchar(50), --主鍵id值

@countname nvarchar(50), --查詢(xún)的字段,排序字段

@sql nvarchar(max) --執(zhí)行內(nèi)部sql

set @tablename=@table

set @filtercolumn=@tableId

set @columnvalue=@id

set @countname=@orderStr

--第一步：處理動(dòng)態(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

--第三步：查詢(xún)操作數(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

--第四步：對(duì)相關(guān)數(shù)據(jù)依次進(jìn)行處理

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

--第三步：查詢(xún)操作數(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

--第四步：對(duì)相關(guān)數(shù)據(jù)依次進(jìn)行處理

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

--第三步：查詢(xún)操作數(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

更多信息請(qǐng)查看IT技術(shù)專(zhuān)欄