上移下移等操作的通用存储过程
写在前面的废话：
忙着做项目，却好久没更新博客了。趁着今天想起，赶快写一篇。
----------------------
数据库：SQL Server 2008
实现的功能：
上移、下移、上移至N位、下移至N位（置顶和置底只需要在传参数的时候传递表的总记录数即可）
注意，此处的排序是修改N条记录的排序字段（我的业务逻辑需要，不考虑大数据量的修改）
数据库表设计
Menu表
MenuNumber菜单序号VARCHAR6
OrderNum排序编号INTEGER
Use [数据库名]GOIF EXISTS (SELECT * FROM sysobjects WHERE name = 'proc_chartdb_menu_list' AND user_name(uid) = 'dbo')DROP PROCEDURE [dbo].proc_chartdb_menu_listGOCREATE PROCEDURE proc_chartdb_menu_list--创建存储过程 ( @Sign int = 0,-- 0： 上移 1：下移 @MoveNum int = 1,-- 移动位数 @TableName nvarchar(50),-- 表名 @ItemName nvarchar(50),-- 主键字段名 @ItemID varchar(12),-- 主键ID值 @SortName nvarchar(50),-- 排序ID名称 @TypeName nvarchar(50)='',-- 条件字段名 @TypeValue nvarchar(50)= ''-- 条件值 ) AS BEGIN SET NOCOUNT ON DECLARE @SQL nvarchar(4000), @ThisSort int,-- 当前排序ID @PREVID varchar(12),-- 前一个主键ID @NextID varchar(12),-- 后一个主键ID @Count int = 0,-- 计数 @TempV varchar(12) = '0'-- 临时变量 --添加事务，并指定语句不能读取已由其他事务修改但尚未提交的行，并且指定，其他任何事务都不能在当前事务完成之前修改由当前事务读取的数据 SET TRANSACTION ISOLATION LEVEL REPEATABLE READBEGIN TRAN A--排序操作 WHILE (@MoveNum > 0) BEGIN IF EXISTS(SELECT * FROM tempdb..sysobjects WHERE id=object_id('tempdb.dbo.#tempTab')) BEGINDROP TABLE #tempTab END --临时索引表-- CREATE TABLE #tempTab ( RowNum int, ItemID varchar(12), Sort int ) --将表中主键和排序字段的值插入到临时表中 SET @SQL = 'INSERT INTO #tempTab (RowNum,ItemID,Sort) SELECT Row,'+@ItemName+ ','+ @SortName +' FROM (SELECT ROW_NUMBER() OVER (ORDER BY '+@SortName+' ASC)AS Row ,'+@ItemName+ ','+ @SortName +' FROM '+ @TableName +') AS TEMPTABLE' IF (@TypeName<>'' AND @TypeValue<>'') SET @SQL = @SQL +' WHERE '+ @TypeName +'='+ @TypeValue --SET @SQL = @SQL +' ORDER BY '+@SortName+' ASC ' EXEC(@SQL) SET @SQL = '' SELECT @Count = COUNT(*) FROM #tempTab SELECT @ThisSort = RowNum FROM #tempTab WHERE ItemID = @ItemID IF(@ThisSort>1) SELECT @PREVID = ItemID FROM #tempTab WHERE RowNum=(@ThisSort-1) IF(@ThisSort<@Count) SELECT @NextID = ItemID FROM #tempTab WHERE RowNum=(@ThisSort+1) IF(@Sign=0) BEGIN IF(@ThisSort>1) BEGIN SELECT @TempV = Sort from #tempTab where ItemID = @PREVID SET @SQL = 'UPDATE '+ @TableName +' SET '+ @SortName+ '='+ CONVERT(varchar(100),@TempV) +' WHERE '+ @ItemName +' = '+ CONVERT(varchar(100),@ItemID) + ';' SELECT @TempV = Sort from #tempTab where ItemID = @ItemID SET @SQL = @SQL + 'UPDATE '+ @TableName +' SET '+ @SortName +'='+ CONVERT(varchar(100),@TempV) +' WHERE '+ @ItemName +' = '+ CONVERT(varchar(100),@PREVID) END END ELSE BEGIN IF(@ThisSort<@Count) BEGINSELECT @TempV = Sort from #tempTab where ItemID = @NextID SET @SQL = 'UPDATE '+ @TableName+ ' SET '+ @SortName+ '='+ CONVERT(varchar(100),(@TempV)) +' WHERE '+ @ItemName +' = '+ CONVERT(varchar(100),@ItemID) +';' SELECT @TempV = Sort from #tempTab where ItemID = @ItemID SET @SQL = @SQL + 'UPDATE '+ @TableName +' SET '+ @SortName +'='+ CONVERT(varchar(100),@TempV) +' WHERE '+ @ItemName +' = '+ CONVERT(varchar(100),@NextID) END END EXEC(@SQL) SET @MoveNum = @MoveNum-1ENDCOMMIT TRAN A END go --执行 存储过程名 --参数1:（0，上移；1，下移）, --参数2：移动位数 --参数3：表名 --参数4：主键名 --参数5：主键值 --参数6：排序字段名 --参数7：条件字段名 --参数8：条件值 exec proc_chartdb_menu_list 1, 3, 'dbo.Menu', 'MenuNumber','121', 'OrderNum', '', '' Go SELECT * FROM Menu Order by OrderNum