今天终于写出一个通用版的上移下移存储过程,表明,主键名,排序字段名等都是动态的,之前写了个针对特殊表的上移下移操作http://www.cnblogs.com/fjsnail/p/3217331.html,显然显得很不灵活。
在下面的存储过程中,包含了许多写动态存储过程的思想。由于我是新手,尤其是对于动态存储过程(Trans_sql)刚刚接触。接下来一篇写点关于动态存储过程的!顺便总结一下该篇包含的思想!
USE [WeiGou2]
GO
/****** Object: StoredProcedure [dbo].[Proc_MoveUpOrDown2] Script Date: 07/30/2013 14:42:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Proc_MoveUpOrDown2]
@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 --升序是最小排序id
declare @tempSql varchar(200) --中间sql语句,存放最后执行的sql语句
declare @tempField varchar(20) --中间sql语句,存放中间变量
Begin
set @tempsql='select '+@sortField+' as t into ##tempTable from '+@tabelName+ ' where '+@keyFiled+'='+cast(@keyValue as varchar(3))+'' --选择出当前排序编号
exec(@tempsql)
select @currentOrderId=t from ##tempTable --从临时表中选择出排序编号
drop table ##tempTable --删除临时表
--找出原有数据中最大orderId和最小orderId
set @tempSql='select min('+@sortField+') as minKey ,max('+@sortField+') as maxKey into ##tempTable2 from '+@tabelName
exec(@tempSql)
set @minOrderId= (select minKey from ##tempTable2)
set @maxOrderId=(select maxKey from ##tempTable2)
drop table ##tempTable2
if(@orderType=0)
Begin --升序
if(@currentOrderId<>@minOrderId) --当前id不等于最小id才有升序资格
Begin
--查询下一个数据的Id和orderId
set @tempSql='select top 1 '+@keyFiled+' as keyFiled ,'+@sortField+' as sortFiled into ##tempTable3 from '+@tabelName +' where '+@sortField+' < '+cast(@currentOrderId as varchar(3)) +' order by '+@sortField +' desc'
exec(@tempSql)
select @lastId=keyFiled from ##tempTable3
select @lastOrderId=sortFiled from ##tempTable3
--当前orderid的排序编号和上一个对换
set @tempSql='update '+@tabelName+' set '+@sortField+'='+ cast(@currentOrderId as varchar(3))+' where '+@keyFiled+'='+cast(@lastId as varchar(3))
set @tempSql=@tempSql+' update '+@tabelName+' set '+@sortField+'='+cast(@lastOrderId as varchar(3))+' where '+@keyFiled+'='+cast(@keyValue as varchar(3))
exec(@tempSql)
drop table ##tempTable3 --dorp临时表
End
End
else --降序
Begin
if(@currentOrderId<>@maxOrderId) --当前id不等于最大id才有升序资格
Begin
--查询下一个数据的Id和orderId
set @tempSql='select top 1 '+@keyFiled+' as keyFiled ,'+@sortField+' as sortFiled into ##tempTable3 from '+@tabelName +' where '+@sortField+' > '+cast(@currentOrderId as varchar(3)) +' order by '+@sortField +' asc'
exec(@tempSql)
select @nextId=keyFiled from ##tempTable3
select @nextOrderId=sortFiled from ##tempTable3
--当前orderid的排序编号和上一个对换
set @tempSql='update '+@tabelName+' set '+@sortField+'='+ cast(@currentOrderId as varchar(3))+' where '+@keyFiled+'='+cast(@nextId as varchar(3))
set @tempSql=@tempSql+' update '+@tabelName+' set '+@sortField+'='+cast(@nextOrderId as varchar(3))+' where '+@keyFiled+'='+cast(@keyValue as varchar(3))
exec(@tempSql)
drop table ##tempTable3 --dorp临时表
End
End
end
使用方法:exec Proc_MoveUpOrDown2 'id',3,1,'tableName,'orderid'
在sql2008中可以调试存储过程,这无疑给要写非常长的存储过程提供了极大的方便。(查看该片 http://www.cnblogs.com/fjsnail/p/3225382.html)