Dynamic mysql stored procedure order with mixed types

Question

I create a stored procedure in which I pass the "order" dynamically as follows:

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 convert all non-VARCHAR fields to VARCHAR using CONCAT, because mixing the possible order types does not work as described here http://www.4guysfromrolla.com/webtech/010704-1.shtml .

My problem is that now the order by name works, but not string orders are returned as 1,10,11,2,3,4,5,6,7,8,9. etc.

Is there a way to use the dynamic order of a mixed data type and still return in the correct order for int, datetimes, etc.

Answer 1

The solution is to create the SQL statement dynamically, so that

 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 information: http://rpbouman.blogspot.com/2005/11/mysql-5-prepared-statement-syntax-and.html

Answer 2

You must use the prepared instructions to create and execute a custom query, or you must write a large CASE condition with all your queries, that is -

  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;