[MYSQL] SELECT 문에서 ORDER BY 조건 동적으로 설정

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
       -- CASE WHEN THEN END 활용한 ORDER BY 조건 설정 
       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 ;

 

원문 : https://www.aspforums.net/Threads/162034/Dynamic-sorting-in-Stored-Procedure-using-MySQL/