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.
Subject
Views
Written By
Posted
How to vary order in a stored proc using a parameter
3960
April 15, 2005 10:09AM
2116
May 06, 2005 02:07AM
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.