上移下移存儲過程

改存儲過程只有兩個參數，要移動的id和排序的方式。tabelname需要動態的改變！

WG_sysConfig是要改變的哦tabelName，id是排序主鍵，orderid是排序字段！

本來我是打算寫個適合全部數據的存儲過程，可總是實現不了 ，熱心的博友請幫我看看端代碼

USE [WeiGou] GO /****** Object: StoredProcedure [dbo].[pro_moveUpOrDown] Script Date: 07/26/2013 16:10:00 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER procedure [dbo].[pro_moveUpOrDown] @Id as int, --數據標志id @orderType as int --0升序，1降序Asdeclare @currentOrderId int --當前標志Id的排序id declare @nextId int --下一個數據標志Id declare @nextOrderId int --下一個數據的排序id declare @lastId int --上一個數據的標志Id declare @lastOrderId int --上個數據的排序id declare @temple varchar(10) --操作過程中varvhar 和int之間的轉換 declare @maxOrderId int --降序時最大排序Id declare @minOrderId int --升序是最小排序iddeclare @sql varchar(200)Begin select @currentOrderId=orderId from WG_sysConfig where id=@Idif(@orderType=0)Begin --升序--if(@currentOrderId=1)-- Begin--當前排序就是1，無需升序-- Endselect @minOrderId=MIN(orderId) from WG_sysConfig --查找最小排序idif(@currentOrderId<>@minOrderId) --當前id不等于最小id才有升序資格Begin--查詢上一個數據的Id和orderIdselect top 1 @lastId=id,@lastOrderId=orderId from Wg_sysConfig where orderId <@currentOrderId order by orderId desc--當前orderid -1 上個+1update WG_sysConfig set orderId=orderId+1 where id=@lastIdupdate WG_sysConfig set orderId=orderId-1 where id=@IdEndEndelse --降序Beginselect @maxOrderId=Max(orderId) from WG_sysConfig --查找最大排序Idselect @maxOrderId--查找最大排序Idif(@currentOrderId<>@maxOrderId) --當前id不等于最大id才有升序資格Begin--查詢上一個數據的Id和orderIdselect top 1 @nextId=id,@nextOrderId=orderId from Wg_sysConfig where orderId >@currentOrderId order by orderId ascselect top 1 @nextId=id,@nextOrderId=orderId from Wg_sysConfig where orderId >@currentOrderId order by orderId asc--當前orderid +1 上個-1update WG_sysConfig set orderId=orderId-1 where id=@nextIdupdate WG_sysConfig set orderId=orderId+1 where id=@IdEndEndend

　　

糾結的代碼

USE [WeiGou] GO /****** Object: StoredProcedure [dbo].[Proc_MoveUpOrDown] Script Date: 07/26/2013 15:06:55 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ================================================ -- Template generated from Template Explorer using: -- Create Procedure (New Menu).SQL -- -- Use the Specify Values for Template Parameters -- ALTER PROCEDURE [dbo].[Proc_MoveUpOrDown]@keyFiled varchar(20), --主鍵字段 @keyValue int , --主鍵值（一般是id，并且設置自動增長） @orderType as int, --0升序，1降序 @tabelName as varchar(20), --表明 @sortField as varchar(20)--排序字段As declare @currentOrderId int --當前標志Id的排序id declare @nextId int --下一個數據標志Id declare @nextOrderId int --下一個數據的排序id declare @lastId int --上一個數據的標志Id declare @lastOrderId int --上個數據的排序id declare @temple varchar(10) --操作過程中varvhar 和int之間的轉換 declare @maxOrderId int --降序時最大排序Id declare @minOrderId int --升序是最小排序iddeclare @tempSql varchar(200) --中間sql語句，存放最后執行的sql語句 declare @tempField varchar(20) --中間sql語句，存放中間變量Begin 在這一步就開始糾結， 我就是想選擇兩個數然后賦值而已，總是實現不了，苦逼啊
--select @currentOrderId=@sortField from WG_sysConfig where @keyFiled=cast(@keyValue as varchar(10)) --選擇出當前排序Id set @tempField=('select '+@sortField+' from WG_sysConfg where '+@keyFiled+'='+CAST(@keyValue as varchar(10))) set @currentOrderId=CAST(@tempField as int) if(@orderType=0)Begin --升序select @minOrderId=MIN(orderId) from WG_sysConfig --查找最小排序idif(@currentOrderId<>@minOrderId) --當前id不等于最小id才有升序資格Begin--查詢上一個數據的Id和orderId-- select top 1 @lastId=@id,@lastOrderId=@sortField from [@tableName] where @sortField <@currentOrderId order by @sortField descset @lastId=('select top 1 '+@keyFiled+' from '+@tabelName +'where '+@sortField+' < '+@currentOrderId +'order by '+@sortField +'desc')set @lastOrderId=('select top 1 '+@sortField+' from '+@tabelName +'where '+@sortField+' < '+@currentOrderId +'order by '+@sortField +'desc')--當前orderid -1 上個+1update WG_sysConfig set orderId=orderId+1 where id=@lastIdupdate WG_sysConfig set orderId=orderId-1 where @keyFiled=@keyValueEndEndelse --降序Beginselect @maxOrderId=Max(orderId) from WG_sysConfig --查找最大排序Idselect @maxOrderId--查找最大排序Idif(@currentOrderId<>@maxOrderId) --當前id不等于最大id才有升序資格Begin--查詢上一個數據的Id和orderId-- select top 1 @nextId=id,@nextOrderId=orderId from Wg_sysConfig where orderId >@currentOrderId order by orderId ascset @nextId=('select top 1 '+@keyFiled+' from '+@tabelName +'where '+@sortField+' > '+@currentOrderId +'order by '+@sortField +'asc')set @nextOrderId=('select top 1 '+@sortField+' from '+@tabelName +'where '+@sortField+' > '+@currentOrderId +'order by '+@sortField +'asc')--當前orderid +1 上個-1set @tempSql='update'+@tabelName+' set '+@sortField+'='+@sortField-1+' where '+@keyFiled+'='+@nextIdset @tempSql=@tempSql+'update'+@tabelName+' set '+@sortField+'='+@sortField+1+' where '+@keyFiled+'='+@nextId-- update WG_sysConfig set orderId=orderId-1 where id=@nextId-- update WG_sysConfig set orderId=orderId+1 where id=@IdEndEndend

　　

?

?

轉載于:https://www.cnblogs.com/fjsnail/p/3217331.html

總結

以上是生活随笔為你收集整理的数据上移下移简单的存储过程的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。