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 [email protected] [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 > >
