mysql stored procedure dynamic order by with mixed types

Question

I'm creating a stored procedure into which i'm passing an "order" dynamically like so:

CREATE PROCEDURE `getStuff`(IN orderSQL VARCHAR(100))
BEGIN
    SELECT id, name, createdate
    FROM mytable
    ORDER BY
        CASE WHEN orderSQL='id_desc' THEN CONCAT(id, '') END DESC,
        CASE WHEN orderSQL='id_asc' THEN CONCAT(id, '') END ASC,
        CASE WHEN orderSQL='name_desc' THEN name END DESC,
        CASE WHEN orderSQL='name_asc' THEN name END ASC,
        CASE WHEN orderSQL='date_desc' THEN CONCAT(createdate, '') END DESC,
        CASE WHEN orderSQL='date_asc' THEN CONCAT(createdate, '') END ASC
END

As you can see i'm converting all the non VARCHAR fields to VARCHAR using CONCAT, because mixing possible order types doesn't work as explained here http://www.4guysfromrolla.com/webtech/010704-1.shtml.

My issue is that now ordering by name works, but non string orders come back as 1,10,11,2,3,4,5,6,7,8,9. etc

Is there a way to use mixed datatype dynamic ordering, and still return in the correct order for ints, datetimes etc.

Answer 1

The solution is to create the SQL statement full dynamically like so

DELIMITER $$

CREATE PROCEDURE GetStuff (IN orderSQL varchar(100) ) 
BEGIN

  DECLARE SQLStatement varchar(255);

  -- Enter the dynamic SQL statement into the
  -- variable @SQLStatement
  SET SQLStatement = CONCAT('SELECT id, name, createdate ',
                             'FROM mytable ',
                             'ORDER BY ',orderSQL);

  PREPARE stmt FROM SQLStatement;
  EXECUTE stmt;
END $$

DELIMITER ;

See here for more info: http://rpbouman.blogspot.com/2005/11/mysql-5-prepared-statement-syntax-and.html

Answer 2

You should use prepared statements to build and execute custom query or you should write big CASE condition with all your queries, i.e. -

  CASE orderSQL
    WHEN 'id_desc' THEN
      SELECT id, name, createdate FROM mytable ORDER BY id;
    WHEN 'id_desc' THEN
      SELECT id, name, createdate FROM mytable ORDER BY id_desc;
    ...
    ...
    ...
  END CASE;