存储过程只是针对【排序字段】是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
过程说明:上下移动排序
创建时间: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