MySQL Forums
Forum List  »  Stored Procedures

How to vary order in a stored proc using a parameter
Posted by: Ian Rose
Date: April 15, 2005 10:09AM

Sorry if this is a duplicate post, but I couldn't find it mentioned yet.

How can I vary the ORDER BY field in mysql within a stored proc using one of the parameters? In SQL Server I can do this:

SELECT Field1, Field2, Field3
FROM MyTable
ORDER BY CASE @OrderParam WHEN 'Field1' THEN Field1 WHEN 'Field2' THEN Field2 ELSE Field3 END

where @OrderParam is a varchar input paramerter of the stored proc. This lets me alter the ORDER BY field at runtime, so

MyProc 'Field1'

produces the same results as

MyProc 'Field2'

but sorted by Field1 rather than Field2.

I can call procs OK in mysql with a fixed ORDER BY clause but not a variable one as above. I can also use CASE... END to vary the output field in a SELECT clause, but can't combine it to produce the required varying order effect. Help!

Any ideas? Or will I just have to write loads of similar stored procs, one for each sort field?

ta.

Options: ReplyQuote


Subject
Views
Written By
Posted
How to vary order in a stored proc using a parameter
3960
April 15, 2005 10:09AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.