Databases
StackOverflow2013
Postsdbo
POOrder by stored procedure parameter
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POOrder by stored procedure parameter
    primarykey
    data
    text
    <p>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.</p> <p>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).</p> <pre><code>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 &lt;= 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; </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USMahmoud Gamal
    1. USAndreas Baran
    plurals
    1. This table or related slice is empty.
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POOrder by stored procedure parameter
      1. This table or related slice is empty.
      1. VTUpMod
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload