Order by stored procedure parameter

I have a problem ordering my stored procedure by a parameter given by a user, I have tried reading but the solutions I have tried won’t work.

So, is there somebody who can help me? I would like it to be sorted by Type from my Group_concat. It works if I type it in harcoded, like (ORDER BY Type1, ORDER BY Type2 and so on). But I would like something like this (ORDER BY @specificStat).

CREATE PROCEDURE getSpecificStatsBySeason(IN khID INT(11), IN seasonName varchar(40), IN specificStat INT(11))
BEGIN

SET @sql = NULL;
SET @khID = khID;
SET @seasonName = seasonName;
SET @specificStat = specificStat;

SELECT
  GROUP_CONCAT(DISTINCT CONCAT('SUM(IF(em.eventTypeID = ',
      et.eventTypeID, ', 1, 0)) AS Type',
      et.eventTypeID )
  ) INTO @sql
FROM  eventTypes       AS et, `eventType-R-kh` AS eRk, kh AS kh WHERE et.eventTypeID = eRk.eventTypeID AND eRk.kh = kh.kh AND kh.khID = @khID;

SET @sql = CONCAT('SELECT u.userID, ud.firstname, ud.lastname,', @sql,
                  ',
                  IFNULL((SELECT COUNT(s.activityID)
                          FROM activity AS a1, signup AS s
                          WHERE u.userid = s.userid
                          AND s.activityID = a1.activityID
                          AND a1.khID = @khID
                            AND s.answer = 1
                            AND se.seasonName = @seasonName
          AND a1.activityDate BETWEEN se.seasonStart AND se.seasonEnd
                  AND a1.activityDate <= NOW()), 0) AS matchCount
                  FROM userData AS ud, userInfo AS u
                  LEFT JOIN activity      AS  a ON a.khID = @khID
                  RIGHT JOIN season AS se ON se.seasonName = @seasonName
          AND a.activityDate BETWEEN se.seasonStart AND se.seasonEnd
                  LEFT JOIN `eventMatch`       AS em ON em.userid = u.userid
                  AND em.activityID = a.activityID
                  LEFT JOIN activityMatch AS am ON a.activityID = am.activityID 
                  WHERE u.khID  = @khID
                  AND u.userID = ud.userID
                  GROUP BY u.UserID  
                  ORDER BY  @specificStat  ;');


prepare stmt 
FROM @sql;

execute stmt;

Answer

Use this instead:

...
...
  AND u.userID = ud.userID
              GROUP BY u.UserID  
              ORDER BY ',   @specificStat, '  ;');

Advertisement

Answer