mysql stored procedure - order by column index

Question

I'm wrining a stored procedure in mysql and I want to use a parameter for the column index in the order-by-clause. I've tried the following:

CREATE PROCEDURE `testProc` (
  IN $sortColNum INT
)
BEGIN
SELECT id, title, date, sticky, published, created, updated, content
FROM news 
ORDER BY $sortColNum DESC;
END

The stored procedure doesn't throw an error, but the result is unsorted. When i use the column index as a parameter in a prepared statement, it works fine. Why doesn't it work in a stored procedure?

Answer 1

Try this one:

CREATE PROCEDURE `testProc`(IN sortColNum INT)
BEGIN
    SET @query = CONCAT ('SELECT id, title, date, sticky, published, created, updated, content
FROM news ORDER BY (',sortColNum,') DESC');
    PREPARE stmt FROM @query;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END