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$$
@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$$
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(*) INTO _Count FROM _Tab ;
SELECT Sort INTO ThisSort FROM _Tab WHERE _ItemID = ItemID;
IF(ThisSort>1) THEN
SELECT _ItemID INTO PREVID FROM _Tab WHERE _Sort=(ThisSort-1);
END IF;
IF(ThisSort<_Count) THEN SELECT _ItemID INTO NextID 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$$语法没有问题,不知道结果如何
就是这个帖子的续。
语法并不复杂,如果你熟悉SQL SERVER的T-SQL语法,则对照着看一下MYSQL的语法文档就可以,最多只需要20 min,哪怕你从来没用过MYSQL。把你写得错的那个贴出来,同时把错误信息一起贴出来。