Paging in MySQL stored procedure

2014-11-20

MySQL database, custom stored procedure queries the data in the table, with paging functionality. The following code is the example:

1DROP PROCEDURE IF EXISTS' sampledb '. ' Proc_getpageddataset ';2 3 CREATEDefiner=' Root ' @ '%`PROCEDURE' Proc_getpageddataset ' (4     inchTableNameVARCHAR( -),/*Table name5   in pageIndex INT,/* Current page*/6     inchPageSizeINT,/*number of records per page*/7Out PageCountINT,/*Total records divided by pages*/8Out Totalrecordcountint /*Total Record Count*/)9 BEGINTen    /*get the number of records in a table*/ One    Set @recordCount=0; A    Set @sql="'; -    Set @sql=CONCAT ('Select COUNT (*) into @recordCount from', TableName);
 -    Preparestmt from @sql;/*preprocessing custom SQL strings*/ the    Executestmt/*execute a custom SQL statement*/ -    deallocate Preparestmt/*Releasing preprocessing resources*/ -     -    SetTotalrecordcount= @recordCount;/*Total Record Count*/ +     -    /*calculate how many pages are returned*/ +    Set @tmp=1; A    Set @tmp=@recordCountMoD pageSize;/*take the remainder*/ at    if @tmp=0  Then -      SetPageCount=@recordCountDiv pageSize; -    Else -      SetPageCount=@recordCountDiv pageSize+ 1; -    End if; -  in    /*pagination display of data*/ -    Set @sql=CONCAT ('SELECT * from', TableName,'Limit', (PageIndex-1)*PageSize,',', pageSize);
 to    Preparestmt from @sql;/*preprocessing custom SQL strings*/ +    Executestmt/*execute a custom SQL statement*/ -    deallocate Preparestmt/*Releasing preprocessing resources*/ the END

Paging in MySQL stored procedure