希望各位前辈指导:CREATE PROCEDURE `pagelist`
(
IN tbname VARCHAR(10),
IN pagesize INTEGER(11),
IN pageno INTEGER(11),
IN conditionc VARCHAR(200),
IN sortcolumn VARCHAR(10),
IN sort INTEGER(11),
OUT totalsize INTEGER(11)
)
DETERMINISTIC
SQL SECURITY DEFINER
COMMENT ''
BEGIN DECLARE @wherestr varchar(200);
DECLARE @sqlselect varchar(200);
IF conditionc='' THEN
set @wherestr = '';
ELSE
set @wherestr =conditionc;
END IF; select count(*) from into totalsize '+tbname+' '+wherestr+'; IF sort=0 THEN
set @sqlselect= CONCAT('SELECT * FROM', tbname 'LIMIT', pageno ',' pagesize @wherestr sortcolumn 'ORDER' ,'ASC');
ELSE
set @sqlselect= CONCAT('SELECT * FROM', tbname 'LIMIT' pageno ',' pagesize @wherestr sortcolumn 'ORDER', 'DESC');
END IF; PREPARE stm FROM @sqlselect;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;END;
(
IN tbname VARCHAR(10),
IN pagesize INTEGER(11),
IN pageno INTEGER(11),
IN conditionc VARCHAR(200),
IN sortcolumn VARCHAR(10),
IN sort INTEGER(11),
OUT totalsize INTEGER(11)
)
DETERMINISTIC
SQL SECURITY DEFINER
COMMENT ''
BEGIN DECLARE @wherestr varchar(200);
DECLARE @sqlselect varchar(200);
IF conditionc='' THEN
set @wherestr = '';
ELSE
set @wherestr =conditionc;
END IF; select count(*) from into totalsize '+tbname+' '+wherestr+'; IF sort=0 THEN
set @sqlselect= CONCAT('SELECT * FROM', tbname 'LIMIT', pageno ',' pagesize @wherestr sortcolumn 'ORDER' ,'ASC');
ELSE
set @sqlselect= CONCAT('SELECT * FROM', tbname 'LIMIT' pageno ',' pagesize @wherestr sortcolumn 'ORDER', 'DESC');
END IF; PREPARE stm FROM @sqlselect;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;END;
DECLARE @sqlselect varchar(200);
IF conditionc='' THEN
set @wherestr = '';
ELSE
set @wherestr =conditionc;
END IF; select count(*) from into totalsize '+tbname+' '+wherestr+';
红字部分有问题。
1。不要加@在内部变量前,MYSQL与SQL SERVER在这一点上有些不同
2。into totalsize '+tbname+' '+wherestr+'; 这种语法并不被支持,你需要用PREPARE / EXECUTE 这种方法。
所以就需要搞点存储过程和触发器。来减少代码量。可惜以前一直都是在用SQL2000/2005的,
这上面的存储过程还是套用SQL2000上面的呢?呵呵 。。