Dynamic sorting in Stored Procedure using MySQL

Last Reply on Jul 28, 2016 06:50 PM By zzz

Posted on Jul 28, 2016 03:09 AM

Hello I would like to ask how to use Dynamic Order By in Stored Proc using MySQL.

http://www.aspsnippets.com/Articles/Custom-Paging-in-ASPNet-GridView-using-MySql-Stored-Procedure.aspx

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `GetCustomers_Pager`(
   _PageIndex INT
   ,_PageSize INT
   ,_SortExpression VARCHAR(50)
   ,_SortDirection VARCHAR(50)
   ,OUT _RecordCount INT
)
BEGIN
       SET @RowNumber:=0;
 
       CREATE TEMPORARY TABLE Results
       SELECT @RowNumber:=@RowNumber+1 RowNumber
              ,CustomerID
              ,ContactName
              ,CompanyName
       FROM Customers;
 
       SET _RecordCount =(SELECT COUNT(*) FROM Results);
 
       SELECT * FROM Results
       WHERE RowNumber BETWEEN(_PageIndex -1) * _PageSize + 1 AND(((_PageIndex -1) * _PageSize + 1) + _PageSize) - 1;
 
       DROP TEMPORARY TABLE Results;
END$$
DELIMITER ;

I've been Stock on how to apply the SORTEXPRESSION and SORTDIRECTION.

 

Thanks!

You are viewing reply posted by: zzz on Jul 28, 2016 06:50 PM.
Posted on Jul 28, 2016 06:50 PM

Hi I made it this way

 

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `GetCustomers_Pager`(
   _PageIndex INT
   ,_PageSize INT
   ,_SortExpression VARCHAR(50)
   ,_SortDirection VARCHAR(50)
   ,OUT _RecordCount INT
)
BEGIN
       SET @RowNumber:=0;
 
       CREATE TEMPORARY TABLE Results
       SELECT @RowNumber:=@RowNumber+1 RowNumber
              ,CustomerID
              ,ContactName
              ,CompanyName
       FROM Customers
	   ORDER BY 
		CASE WHEN _SortExpression = 'CustomerID' AND _SortDirection='ASC' THEN CustomerID END ASC,
		CASE WHEN _SortExpression = 'CustomerID' AND _SortDirection='DESC' THEN CustomerID END DESC,
		CASE WHEN _SortExpression = 'ContactName' AND _SortDirection='ASC' THEN ContactName END ASC,
		CASE WHEN _SortExpression = 'ContactName' AND _SortDirection='DESC' THEN ContactName END DESC,
		CASE WHEN _SortExpression = 'CompanyName' AND _SortDirection='ASC' THEN CompanyName END ASC,
		CASE WHEN _SortExpression = 'CompanyName' AND _SortDirection='DESC' THEN CompanyName END DESC
		;
 
       SET _RecordCount =(SELECT COUNT(*) FROM Results);
 
       SELECT * FROM Results
       WHERE RowNumber BETWEEN(_PageIndex -1) * _PageSize + 1 AND(((_PageIndex -1) * _PageSize + 1) + _PageSize) - 1;
 
       DROP TEMPORARY TABLE Results;
END$$
DELIMITER ;

If you have shorter codes, kindly comment here!

 

Thanks!