- 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
- --该片段来自于http://www.codesnippet.cn/detail/040920135600.html
来源: http://www.codesnippet.cn/detail/040920135600.html