SQL 上下移动排序

 

存储过程只是针对【排序字段】是datetime类型设计 有局限性，欢迎批评指正

/*
 过程说明:上下移动排序 
 创建时间:2010年1月12日
 作者:fengxb
 debug:存储过程只是针对【排序字段】是datetime类型设计 有局限性
*/ 
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

alter PROCEDURE [dbo].[P_Move]
     (
     @keyByteName varchar(20)='fun_id',  --主键字段名称
     @keyByteNameValue varchar(100)='14', --主键值
     @tableName varchar(80)='touch_Recreation',  --表名
     @moveType varchar(10)='up',        --移动方向
     @sortByteName varchar(20)='fun_no',  --排序字段
     @sWhereIf varchar(800)='' --查询条件
)
 as 
   
   declare @SQL nvarchar(4000)
   declare @tempID nvarchar(20)
   declare @tempCursorID nvarchar(20)
   declare @tempCursorOrder datetime
   declare @VarID nvarchar(20)
   declare @VarOrder datetime
   set @VarID=''
   
    --创建临时表

  Create Table #temp_moveTable
   (
     temp_id [nvarchar](100) COLLATE Chinese_PRC_CI_AS NULL,
     temp_order datetime,
   )
   
   if @moveType='up'
      begin
        if @sWhereIf=''
            set @SQL = N'insert into #temp_moveTable select top 2 '+@keyByteName+','+@sortByteName+' from '+@tableName+' where '+@keyByteName+'<='+@keyByteNameValue+' order by '+@sortByteName+' desc' 
        else
          set @SQL = N'insert into #temp_moveTable select top 2 '+@keyByteName+','+@sortByteName+' from '+@tableName+' where '+@keyByteName+'<='+@keyByteNameValue+' and '+@sWhereIf+' order by '+@sortByteName+' desc'  
      end
    if @moveType='down'
      begin
        if @sWhereIf=''
          set @SQL = N'insert into #temp_moveTable select top 2 '+@keyByteName+','+@sortByteName+' from '+@tableName+' where '+@keyByteName+'>='+@keyByteNameValue+' order by '+@sortByteName+' asc' --order by list_order asc
        else
          set @SQL = N'insert into #temp_moveTable select top 2 '+@keyByteName+','+@sortByteName+' from '+@tableName+' where '+@keyByteName+'>='+@keyByteNameValue+' and '+@sWhereIf+' order by '+@sortByteName+' asc'  
      end
     if @moveType='top'
      begin
        if @sWhereIf=''
          set @SQL = N'insert into #temp_moveTable select top  '+@keyByteName+','+@sortByteName+' from '+@tableName+' where '+@keyByteName+'<='+@keyByteNameValue+' order by '+@sortByteName+' desc' --order by list_order asc
        else
          set @SQL = N'insert into #temp_moveTable select top  '+@keyByteName+','+@sortByteName+' from '+@tableName+' where '+@keyByteName+'<='+@keyByteNameValue+' and '+@sWhereIf+' order by '+@sortByteName+' desc'  
      end
     if @moveType='bottom'
      begin
        if @sWhereIf=''
          set @SQL = N'insert into #temp_moveTable select top  '+@keyByteName+','+@sortByteName+' from '+@tableName+' where '+@keyByteName+'>='+@keyByteNameValue+' order by '+@sortByteName+' asc' --order by list_order asc
        else
          set @SQL = N'insert into #temp_moveTable select top  '+@keyByteName+','+@sortByteName+' from '+@tableName+' where '+@keyByteName+'>='+@keyByteNameValue+' and '+@sWhereIf+' order by '+@sortByteName+' asc'  
      end
   exec(@SQL)

   declare temp_move_cousor cursor for select temp_id,temp_order from #temp_moveTable
   open  temp_move_cousor 
   fetch next from temp_move_cousor into @tempCursorID,@tempCursorOrder
   while  @@fetch_status=0 
    begin
      if @VarID=''
         begin 
            set @VarID=@tempCursorID   
            set @VarOrder=@tempCursorOrder
         end
      else
         begin
           select top 2 fun_id,fun_no from touch_Recreation where fun_id<=14 and  mr_id=44 and mr_type=0  order by fun_no desc
           set @SQL = N'update '+@tableName+' set '+@sortByteName+' =@tempInOrder1 where '+@keyByteName+' = '+@tempCursorID+'  
                        update '+@tableName+' set '+@sortByteName+' =@tempInOrder2  where '+@keyByteName+' = '+@VarID+' 
                        select @tempOutOrder = '+@sortByteName+' from '+@tableName+' where '+@keyByteName+' = '+@VarID+' ' 
         EXECUTE  sp_executesql @SQL,N'@tempOutOrder datetime output,@tempInOrder1 datetime,@tempInOrder2 datetime',@tempOutOrder= @VarOrder,@tempInOrder1=@VarOrder,@tempInOrder2=@tempCursorOrder
         end  
      fetch next from temp_move_cousor into @tempCursorID,@tempCursorOrder
    end
    close temp_move_cousor                   --关闭游标
    deallocate temp_move_cousor 
  
  if exists (select   1   from   tempdb..sysobjects where name like '#temp_moveTable%'  )  
   begin
     drop table #temp_moveTable 
   end