Paging Sorting Searching in MySQL Procedure

Hi All,

Lets do some mySQL programming in the last post i have shown how to do custom pagination from jQuery Data table and today i will share the procedure that i called at the back end to do the pagination stuff this one is in mySQL

USE `Database_Name`;

DROP procedure IF EXISTS `up_GlobalSearch`;



DELIMITER $$

USE `Database_Name`$$

CREATE PROCEDURE `scientestdb`.`up_GlobalSearch` (

IN pFreeText varchar(200), 

IN pStartRecord int, 

IN pSize int, 

IN pSortColumn varchar(20),

IN pSortDirection varchar(20)

)

BEGIN





SET @q := concat('select * from Table_Name where MATCH (Column_Name1,Column_Name2) AGAINST (\'', pFreeText ,'\'  IN BOOLEAN MODE)  ORDER By ', pSortColumn, ' ' , pSortDirection, ' limit ?,?');

PREPARE stmt1 FROM @q;

SET @recStrt = pStartRecord;

SET @recCount = pSize;

EXECUTE stmt1 USING @recStrt, @recCount;





END

$$



DELIMITER ;

Hope it helps.

Happy Coding.

Regards,
U