Thanks Svein, , with the addition of : before
(select iif(:index_by = 1, myfield1, myfield2), iif(:index_by = 1,

> myfield2, myfield1), myfield1, myfield2
> from mytable)

that does work exactly as I want.
The reason for wanting the order by clause in the SP is because the
database is queried from Delphi, browser-based queries and now Android
devices and I would have to maintain more code with the potential for
more errors. The real-life indexes are also multi-table using aliases.

Thanks again, I'll now try and convert that example.

Alan J Davies
Aldis


On 22/10/2014 07:22, Svein Erling Tysvær
svein.erling.tysvaer@... [firebird-support] wrote:

> >create or alter procedure my_SP1 (
> > index_by integer) /* the order I want */ returns (
> > myfield1_out char(15),
> > myfield2_out char(15))
> >AS
> >begin
> > for select
> > myfield1,myfield2
> > from mytable
> > case
> > when index_by=1
> > then order by myfield1,myfield2
> > when index_by=2
> > then order by myfield2,myfield1
> > end
> > into :myfield1_out,:myfield2_out
> > do
> > suspend;
> >end
>
> OK Alan, I don't quite understand why you want the order by in the
> stored procedure itself, the standard way to do what you want would be
> to have:
>
> create or alter procedure my_SP1
> returns (
> myfield1_out char(15),
> myfield2_out char(15))
> AS
> begin
> for select
> myfield1,myfield2
> from mytable
> into :myfield1_out,:myfield2_out
> do
> suspend;
> end
>
> And then have the ORDER BY in the call to the stored procedure, i.e.
>
> SELECT myfield1_out, my_field2_out
> from my_SP1()
> order by myfield1_out, my_field2_out
>
> or
>
> SELECT myfield1_out, my_field2_out
> from my_SP1()
> order by my_field2_out, myfield1_out
>
> However, if the field definition for myfield1 and myfield2 is identical
> (or at least similar), I think it should be possible to get what you say
> you want (I haven't actually tried ORDER BY in a SP, but expect it to
> work):
>
> create or alter procedure my_SP1 (
> index_by integer) /* the order Alan wants */ returns (
> myfield1_out char(15),
> myfield2_out char(15))
> AS
> begin
> for with tmp (OrderBy1, OrderBy2, myfield1, myfield2) as
> /* IIF is just a shortcut for CASE */
> (select iif(index_by = 1, myfield1, myfield2), iif(index_by = 1,
> myfield2, myfield1), myfield1, myfield2
> from mytable)
> select myfield1, myfield2
> from tmp
> order by OrderBy1, OrderBy2
> into :myfield1_out,:myfield2_out
> do
> suspend;
> end
>
> If myfield1 was an integer and myfield2 a character field, this would
> probably have unexpected results since I would expect both Orderby1 and
> Orderby2 to be translated to a character field before the sorting.
>
> HTH,
> Set
>
>