CREATE PROCEDURE CommonPageProc(

    IN    p_tablename        VARCHAR(1024),      

    IN    p_fields            VARCHAR(1024),      

    IN    p_pagesize            INT,              

    IN    p_pageindex            INT,              

    IN    p_orderfield        VARCHAR(128),      

    IN    p_sqlwhere        VARCHAR(1024),     

    OUT    p_totalrecord            INT    

   

)

    NOT DETERMINISTIC

    SQL SECURITY DEFINER

    COMMENT '分页存储过程'

   

BEGIN

    /*定义变量*/

    DECLARE m_begin_row INT DEFAULT 0;

    DECLARE m_limit_string CHAR(64);

    /*构造语句*/   

    SET m_begin_row = (p_pageindex - 1) * p_pagesize;

    SET m_limit_string = CONCAT(' LIMIT ', m_begin_row, ',', p_pagesize);

    if (p_sqlwhere<>'' ) THEN

     set p_sqlwhere=concat(' where ',p_sqlwhere);

    end if;

    if p_orderfield<>'' then

     set p_orderfield=concat(' order by ',p_orderfield);

     end if;

   

    SET @COUNT_STRING = CONCAT('SELECT COUNT(*) INTO @ROWS_TOTAL FROM ', p_tablename, ' ', p_sqlwhere);

    SET @MAIN_STRING = CONCAT('SELECT ', p_fields, ' FROM ', p_tablename, ' ', p_sqlwhere, ' ', p_orderfield, m_limit_string);

    /*预处理*/

    PREPARE count_stmt FROM @COUNT_STRING;

    EXECUTE count_stmt;

    DEALLOCATE PREPARE count_stmt;

    SET p_totalrecord = @ROWS_TOTAL;

    PREPARE main_stmt FROM @MAIN_STRING;

    EXECUTE main_stmt;

    DEALLOCATE PREPARE main_stmt;

   

END