传入参数表名、当前页、每页大小、查询字段传入自己需要的数据、也可以全部。where条件可以为空。排序字段按自己的需求设定
CREATE PROCEDURE `prc_page_result`(
IN tablename varchar(100), -- 表名
IN currpage int, -- 当前页
IN pagesize int, -- 每页记录数
IN `columns` varchar(500), -- 查询字段
IN sCondition varchar(500), -- where条件
IN sOrder varchar(1000) -- 排序条件
)
BEGIN
declare sSql varchar(4000);
declare sLimit VARCHAR(100);
SET sLimit = concat(' LIMIT ',(currpage-1)*pagesize,',',pagesize);
IF sOrder <> '' THEN
SET sOrder = concat(' ORDER BY ',sOrder);
END IF;
IF sCondition <> '' THEN
SET sSql = concat('SELECT ', `columns`, ' FROM ', tablename, ' WHERE ');
SET sSql = concat(sSql, sCondition, sOrder, sLimit);
ELSE
SET sSql = concat('select ', `columns`, ' from ', tablename, sOrder, sLimit);
END IF;
SET @sQuery = sSql;
PREPARE stmt FROM @sQuery;
EXECUTE stmt;
-- DEALLOCATE PREPARE;
END