| 标题 | SQL数据上移，下移，置顶的存储过程 |
| 范文 | 通用的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 |
| 随便看 |
|
在线学习网范文大全提供好词好句、学习总结、工作总结、演讲稿等写作素材及范文模板，是学习及工作的有利工具。