CREATE DEFINER=`root`@`localhost` PROCEDURE `CommonPagenation`(
in tableName varchar(50),
in selectWhere varchar(500) ,
in selectID varchar(20) ,
in selectOrder varchar(200),
in pageNo int ,
in pageSize int,
out _totalcount int,
out _pagecount int)
begin
set @startrow=(pageNo-1)*pageSize;
set @pagesize = pageSize;
set @rowindex=0;
set @strsql = CONCAT(
'select SQL_CALC_FOUND_ROWS * from ',tableName,
case
IFNULL(selectWhere,'')
when ''
then ''
else CONCAT (' where ',selectWhere)
end,
case
IFNULL(selectOrder,'')
WHEN ''
then ''
ELSE CONCAT(' order by ', selectOrder)
END,
' limit ',
@startRow,
',',
@pagesize
) ;
PREPARE strsql FROM @strsql ;
EXECUTE strsql ;
SET _totalcount = FOUND_ROWS() ;
IF (_totalcount <= pageSize)
THEN SET _pagecount = 1 ;
ELSE IF (_totalcount % pageSize > 0)
THEN SET _pagecount = ceil(_totalcount / pageSize);
ELSE SET _pagecount = _totalcount / pageSize ;
END IF ;
END IF ;
END;
in tableName varchar(50),
in selectWhere varchar(500) ,
in selectID varchar(20) ,
in selectOrder varchar(200),
in pageNo int ,
in pageSize int,
out _totalcount int,
out _pagecount int)
begin
set @startrow=(pageNo-1)*pageSize;
set @pagesize = pageSize;
set @rowindex=0;
set @strsql = CONCAT(
'select SQL_CALC_FOUND_ROWS * from ',tableName,
case
IFNULL(selectWhere,'')
when ''
then ''
else CONCAT (' where ',selectWhere)
end,
case
IFNULL(selectOrder,'')
WHEN ''
then ''
ELSE CONCAT(' order by ', selectOrder)
END,
' limit ',
@startRow,
',',
@pagesize
) ;
PREPARE strsql FROM @strsql ;
EXECUTE strsql ;
SET _totalcount = FOUND_ROWS() ;
IF (_totalcount <= pageSize)
THEN SET _pagecount = 1 ;
ELSE IF (_totalcount % pageSize > 0)
THEN SET _pagecount = ceil(_totalcount / pageSize);
ELSE SET _pagecount = _totalcount / pageSize ;
END IF ;
END IF ;
END;