DROP PROCEDURE IF EXISTS UP_SplitPages;
CREATE PROCEDURE UP_SplitPages(
nCurrPage int,/*当前页*/
nFieldName varchar(1000),/*显示字段*/
nTablename varchar(100),/*表名*/
nWhereStr varchar(1000),/*查询条件*/
nOrderStr varchar(1000),/*排序条件*/
nPageSize int/*每页条数*/
)
BEGIN
DECLARE sBeginRow INT DEFAULT 0;/*行数*/
DECLARE sLimit varchar(1000);
IF nCurrPage<1 THEN
set sBeginRow = 0;
ELSE
set sBeginRow = (nCurrPage-1)*nPageSize;
END IF;
set sLimit = CONCAT(' LIMIT ',sBeginRow,', ',nPageSize);/*limit串 */
IF (nWhereStr is null OR nWhereStr='') THEN
SET @sWhereStr = '' ;
ELSE
SET @sWhereStr =CONCAT(' WHERE ',nWhereStr);
END IF;
IF (nOrderStr is null OR nOrderStr='') THEN
SET @sOrderStr = '' ;
ELSE
SET @sOrderStr =concat(' ORDER BY ',nOrderStr);
END IF;
SET @sql_data = CONCAT('SELECT ',nFieldName,' FROM ',nTablename,@sWhereStr,@sOrderStr,sLimit);
/*SET @sql_count=CONCAT('SELECT COUNT(*) FROM ',nTablename,@sWhereStr);
PREPARE stmtCount FROM @sql_count;
EXECUTE stmtCount;
DEALLOCATE PREPARE stmtCount;*/
PREPARE stmtData FROM @sql_data;
EXECUTE stmtData;
DEALLOCATE PREPARE stmtData;
END;
调用:call UP_SplitPages (2,'RID,GroupID,GroupName,GroupType,Description','Group','1=1 and GroupType<10','group.GroupID','RID',4)
调用:call UP_SplitPages (2,'RID,GroupID,GroupName,GroupType,Description','Group','1=1 and GroupType<10','Group.GroupID','RID',4)
如果存储过程返回有两个结果集,php中不知道如何获取,我没试成功,哪位有好的方式能处理,请留言