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;--这里怎么报错了
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 = 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;--这里怎么报错了
end$$
XXX
else
XXX
end if;
有什么问题SET _SQL = '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))';