Msql paging Stored Procedure

The paging storage process of MySQL. The foreground can call the corresponding parameters to obtain the results. It can also be used in combination with the jqgrid list and the page sharer configuration parameters.

-- ---------------------------------------------------------------------------------- Routine DDL -- Note: comments before and after the routine body will not be stored by the server -- revoke delimiter $ create definer = 'root' @ '% 'Procedure 'fenye' (in p_table_name varchar (100), -- table name in p_fields varchar (500), -- selected field in p_where_string varchar (500), -- filter condition with where in p_order_string varchar (500 ), -- sorting method with order in p_page_now int, -- current page number in p_page_size int, -- number of pages out p_out_rows int -- output parameter, total number of records) begin/* define variable */declare m_begin_row int default 0; declare m_limit_string varchar (128);/* construct statement */set m_begin_row = (p_page_now-1) * p_page_size; set m_limit_string = Concat ('limit', m_begin_row, ',', p_page_size); Set @ count_string = Concat ('select count (*) into @ rows_total from ', p_table_name, '', p_where_string); Set @ main_string = Concat ('select', p_fields, 'from', p_table_name,'', p_where_string, '', p_order_string, m_limit_string ); /* pre-processing */prepare count_stmt from @ count_string; execute count_stmt; deallocate prepare count_stmt; Set region = @ rows_total; prepare region from @ main_string; execute region; deallocate prepare else