>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
