ALTER PROCEDURE [dbo].[Proc_Common_UpDown]
@Sign int = 0, -- 0： 上移 1：下移
@TableName nvarchar(50), -- 表名
@ItemName nvarchar(50), -- 主键字段名
@ItemID int, -- 主键ID
@SortName nvarchar(50), -- 排序ID
@TypeName nvarchar(50)='', -- 分类字段名
@TypeValue nvarchar(50)= '' -- 分类值
AS
BEGIN
SET NOCOUNT ON
DECLARE
@SQL nvarchar(4000),
@ThisSort int, -- 当前ID
@PREVID int, -- 前一个ID
@NextID int, -- 后一个ID
@Count int
--临时索引表--
CREATE TABLE #Tab
(
ItemID int,
Sort int
)
SET @SQL = 'INSERT INTO #Tab (ItemID,Sort) SELECT '
+@ItemName+','+@SortName+' FROM '+@TableName
IF (@TypeName<>'' AND @TypeValue<>'') SET @SQL = @SQL+' WHERE '+@TypeName+'='+@TypeValue
SET @SQL = @SQL+' ORDER BY Sort ASC '
EXEC(@SQL)
SET @SQL = ''
SELECT @Count = COUNT(*) FROM #Tab
SELECT @ThisSort = Sort FROM #Tab WHERE ItemID = @ItemID
IF(@ThisSort>1) SELECT @PREVID = ItemID FROM #Tab WHERE Sort=(@ThisSort-1)
IF(@ThisSort<@Count) SELECT @NextID = ItemID FROM #Tab WHERE Sort=(@ThisSort+1)
IF(@Sign=0)
BEGIN
IF(@ThisSort>1)
BEGIN
SET @SQL = 'UPDATE '+@TableName+' SET '+@SortName+'='+CONVERT(varchar(100),(@ThisSort-1))+' WHERE '+@ItemName+' = '+CONVERT(varchar(100),@ItemID) + ';'
+ 'UPDATE '+@TableName+' SET '+@SortName+'='+CONVERT(varchar(100),@ThisSort)+' WHERE '+@ItemName+' = '+CONVERT(varchar(100),@PREVID)
END
END
ELSE
BEGIN
IF(@ThisSort<@Count)
BEGIN
SET @SQL = 'UPDATE '+@TableName+' SET '+@SortName+'='+CONVERT(varchar(100),(@ThisSort+1))+' WHERE '+@ItemName+' = '+CONVERT(varchar(100),@ItemID) + ';'
+ 'UPDATE '+@TableName+' SET '+@SortName+'='+CONVERT(varchar(100),@ThisSort)+' WHERE '+@ItemName+' = '+CONVERT(varchar(100),@NextID)
END
END
EXEC(@SQL)
END
exec Proc_Common_UpDown 1,'pktable','id','4','sort','',''
转成mysql，100分酬谢！
我改写的没有成功的代码：
delimiter $$
drop procedure if exists ChangeSequence$$
create procedure ChangeSequence
(
_sign int ,-- 0： 上移 1：下移
TableName varchar(50) ,-- 表名
ItemName varchar(50) ,-- 主键字段名
ItemID int , -- 主键ID
SortName varchar(50) , -- 排序ID
TypeName varchar(50) , -- 分类字段名
TypeValue varchar(50) -- 分类值
)
begin
declare _SQL varchar(4000);
declare ThisSort int;
declare PREVID int;
declare NextID int;
declare _Count int;
create temporary table _Tab
(
_ItemID int,
_Sort int
);
set _SQL=concat('INSERT INTO _Tab (_ItemID,_Sort) SELECT ' ,ItemName,SortName,' FROM ',TableName) ;
if (TypeName<>'' and TypeValue<>'') then
set _SQL=concat(_SQL,' where ',TypeName,'=',TypeValue);
end if;
SET _SQL = concat(_SQL,' ORDER BY _Sort ASC ') ;
set @v_sql=_SQL;
prepare stmt from @v_sql;
execute stmt;
deallocate prepare stmt;
SET _SQL = '';
SELECT _Count = COUNT(*) FROM _Tab ;
SELECT ThisSort = _Sort FROM _Tab WHERE _ItemID = ItemID;
IF(ThisSort>1) then
SELECT PREVID = _ItemID FROM _Tab WHERE _Sort=(ThisSort-1);
end if;
IF(ThisSort<_Count) then SELECT NextID = _ItemID FROM _Tab WHERE _Sort=(ThisSort+1);end if;
if(_Sign=0) then
if(ThisSort>1) then
set _SQL = concat('UPDATE ',TableName,' SET ',SortName,'=','CONVERT((',ThisSort,'-1),varchar(100))',' WHERE ',ItemName,' = ','CONVERT(',ItemID,',varchar(100))' , ';' ,
'UPDATE ',TableName,' SET ',SortName,'=','CONVERT(',ThisSort,',varchar(100))',' WHERE ',ItemName,' = ','CONVERT(',PREVID,',varchar(100))');
end if;
else
IF(ThisSort<_Count) then
SET _SQL =concat( 'UPDATE ',TableName,' SET ',SortName,'=','CONVERT((',ThisSort,'+1),varchar(100))',' WHERE ',ItemName,' = ','CONVERT(',ItemID,',varchar(100))',';',
'UPDATE ',TableName,' SET ',SortName,'=','CONVERT(',ThisSort,',varchar(100))',' WHERE ',ItemName,' = ','CONVERT(',NextID,',varchar(100))');
end if;
end if;
set @v_sql1=_SQL;
prepare stmt1 from @v_sql1;
execute stmt1;
deallocate prepare stmt1;
end$$
