Re: Dynamic expressions set in "order by" clause

On Nov 21, 2007, at 9:48 AM, Sergey Konoplev wrote:

> Hello,
>
> I have a procedure which takes few arguments. The arguments must
> affect expressions set of "order by" clause. For instance in one case
> ordering must be performed by 1st, 2nd (desc) and 3rd fields but in
> another by 3rd, 1st and 2nd fields.
>
> Is there a way to manage it without using dynamic queries (execute
> '...') and code duplicating?
>
> --
> Regards,
> Sergey Konoplev

Not that I've ever heard of.  Well, you move the procedure up into
the application level but inside the database you'll need a procedure
to handle the branching on the argument values with each branch
either running a mostly duplicated query (the only difference in the
order  by) or each branch generating the order by expression as a
string which you then use in an EXECUTE statement.  If anyone knows
different, I'd love to hear it.

Erik Jones

Software Developer | Emma®
erik@myemma.com
800.595.4401 or 615.292.5888
615.292.0777 (fax)

Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com