CODEWITHSUNDEEP
mysqlsql
spraff
spraff

Reputation: 33405

SQL variable ORDER BY clauses in stored procedures

I have a SQL query which is currently pasted together from strings in PHP and I want to turn it into a stored procedure.

At the moment it's something like

function db_get ($mode)
{
    $order_by = '';
    if ('name'===$mode)
        $order_by = '`name` DESC';
    if ('date'===$mode)
        $order_by = '`date` DESC';
    // ...

    return db_all_rows ("SELECT foo FROM bar WHERE baz ORDER BY $order_by");
}

I don't think the name of a column can be an argument to a stored procedure, so it seems that either I'll have to copy-paste the whole query for each kind of order-by it needs, or do some kind of numerical hack like this

CREATE PROCEDURE GetStuff (IN use_name INT, IN use_date INT, ...)
    SELECT foo, (name*use_name + date*use_date + ...) AS order_by_value
    FROM bar WHERE baz ORDER BY order_by_value

I'm not sure which is worse.

I'll prefer the numerical hack to the duplication, unless it will screw up the optimiser.

Will it screw up the optimiser?

Or better yet, can I properly parameterise the order-by clause?

Upvotes: 1

Views: 825

Answers (1)

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 726669

Try this:

SELECT foo
FROM bar
WHERE baz
ORDER BY
    case when $order_by='name' then name else null end
,   case when $order_by='date' then date else null end

The idea is to construct a list of all possible ORDER BY columns, and protect each one with a when condition so that only one of them is "active".

Upvotes: 2

Related Questions