一个分页存储过程的问题
搞了好久都没有搞好，那位大哥帮个忙帮我看一下：
DELIMITER $$
DROP PROCEDURE IF EXISTS `huangxin`.`pagination_mysql` $$
CREATE PROCEDURE `huangxin`.`pagination_mysql` (
in currpage int,
in columns varchar(500),
in tablename varchar(500),
in sCondition varchar(500),
in order_field varchar(100),
in asc_field int,
in primary_field varchar(100),
in pagesize int
)
BEGIN
declare sTemp varchar(1000);
declare sSql varchar(4000);
declare sOrder varchar(1000);
if asc_field = 1 then
set sOrder = concat( ' order by ',order_field, ' desc ');
set sTemp = ' <(select min ';
else
set sOrder = concat( ' order by ',order_field, ' asc ');
set sTemp = '> )select max ';
end if;
if currpage = 0 then
if sCondition <> ' ' then
set sSql = concat( 'select ',columns, ' from ',tablename, ' where ');
set sSql = concat(sSql,sCondition,sOrder);
else
set sSql = concat( 'select ',columns, ' from ',tablename,sOrder);
end if;
else
if sCondition <> ' ' then
set sSql = concat( 'select ',columns, ' from ',tablename);
set sSql = concat(sSql, ' where ',sCondition, ' and ',primary_field,sTemp);
set sSql = concat(sSql, '(private_key) ', ' from (select ');
set sSql = concat(sSql,primary_field, ' as private_key from ', tablename, ' where ',sCondition, sOrder);
set sSql = concat(sSql, ' limit ',(currpage-1)*pagesize, ', ', pagesize, ') as tabtemp) ', sOrder);
/*set sSql = concat(sSql, ' limit ? ');*/
else
set sSql = concat( 'select ',columns, ' from ',tablename);
set sSql = concat(sSql, ' where ', primary_field, sTemp);
set sSql = concat(sSql, '(private_key) ', ' from (select ');
set sSql = concat(sSql,primary_field, ' as private_key from ', tablename, sOrder);
set sSql = concat(sSql, ' limit ',(currpage-1)*pagesize, ', ', pagesize, ') as tabtemp) ', sOrder);
/*set sSql = concat(sSql, ' limit ? ');*/
end if;
end if;
set @iPageSize = pagesize;
set @sQuery = sSql;
prepare stmt from @sQuery;
execute stmt using @iPageSize;
END $$
DELIMITER ;
运行的代码是这样：call pagination_mysql(1, '* ', 'book_search ', ' ', 'book_id ', 1, 'book_id ', 8);
出现的结果是：Incorrect arguments to EXECUTE
那位大哥帮个忙.小弟被BS啊。
[解决办法]
当currpage = 0时，SQL文里没有”？”参数
[解决办法]
简单错误,把语句打印出来,一步步调试就可以了
我没仔细看啊~~~~~~~~~~~
[解决办法]
execute stmt using @iPageSize;
====
execute stmt；
[解决办法]
set @iPageSize = pagesize;
set @sQuery = sSql;
去手册上看看
limit ?
和 limit 具体的参数
的区别。
