I have a stored procedure that has to return a table after filtering rows based on input. Two of the inputs are sort_column and sort_dir. The query must ORDER BY sort_column in the sort_dir direction (ASC or DESC).

I tried the following query without success. The query below has been simplified to include only relevant terms. Other filters work fine without any issues.

1. SELECT * FROM table ORDER BY sort_column sort_dir

2. SELECT * FROM table ORDER BY CASE sort_column when 'col1' then col1_name WHEN 'col2' THEN col2_name END CASE sort_dir WHEN 'asc' THEN ASC ELSE DESC END

3. I connected 2 inputs to 1 input in _ format and tried the following:

   SELECT * FROM table ORDER BY CASE sort_input
     WHEN 'col1_asc' THEN col1_name ASC
     WHEN 'col1_desc' THEN col1_name DESC
     WHEN 'col2_asc' THEN col2_name ASC
     WHEN 'col2_desc' THEN col2_name DESC END

I keep getting error #1064. It's different in each of the above cases, but always points to the "CASE" section. This is a bug with option 2 mentioned above

##1064 - There is an error in your SQL syntax; check the manual for your MySQL server version for 'WHEN 'col1' THEN col1_name END CASE 'asc' WHEN 'desc on line 4 Correct syntax to use around ' THEN DESC ELSE '

The problem doesn't seem to be the column names. This is a sorting direction that doesn't work. If I try each of the above options without the "ASC" and "DESC" parts, there is no problem.

Am I doing something wrong here? Is there a better way besides CASE?

MySQL version: 5.6