Helen
This is a fuller version of the SP with a lot of detail omitted for
clarity. I'm now worried based on what you are saying, but I can
confirm that it does compile with no errors. However, the data
returned is not in the correct order. When Code order is selected - an
integer field - the data is returned as follows, even if I
cast(Supp_Code as integer):
not
1 1
10 2
11 3
2 4
20 5
So Firebird is converting this to a character field because - as you
are saying - this is what is expected when its compiled. So it looks
like I can't improve the readability and am stuck with if... else...
Even though I had hoped to be able to do what I wanted, at least I
have a reasoned answer and will reatin the existing SPs, so thanks for
that.

I run this SP via a select from within Delphi and others also use Php,
which is why I want the "order by" clause inside the SP, not in the
applications.
qry3.Sql.Clear;
qry3.Sql.Add('Select * GET_HAULIER_ADDRESS(:locCode,:DisplayBy,:IndexBy)');
qry3.Params.ParamValues['locCode']:=locCode;
qry3.Params.ParamValues['DisplayBy']:=DisplayBy;
qry3.Params.ParamValues['IndexBy']:=IndexBy;
qry3.Open;


CREATE PROCEDURE GET_HAULIER_ADDRESS (
loccode integer,
displayby integer,
indexby integer)
returns (
code integer,
name char(40)
as
begin
if (:DISPLAYBY=0) then /* Display Individual */
begin
for select Haul_Code,Haul_Name
from Haulier
where Haul_Code=:locCode
order by case when (:indexby)=0 then Haul_Name
else Haul_Code
end
into :Code,:Name
do
suspend;
end
else if (:DISPLAYBY=1) then /* Display All */
begin
for select Haul_Code,Haul_Name
from Haulier
order by case when (:indexby)=0 then Haul_Name
else Haul_Code
end
into :Code,:Name
do
suspend;
end
end
--
Alan J Davies
Aldis



Quoting Helen Borrie <helebor@...>:

> At 07:29 PM 30/05/2007, you wrote:
>> Hi - I'm in the process of converting a number of SPs from an
>> If..Then..Else construct to a Case... construct because in a number of
>> them the only part which changes is the index.
>> Using FB 1.5.3 Windows 2003 server and IB Expert. Lots of ram and disk.
>>
>> This works (and has already been applied to a number of SPs)
>>
>> order by case when (:indexby)=0 then Supp_Name /* name index
>> else Supp_Code end /* code index
>
> I don't think so. ORDER BY is structural in a SP, i.e. it has to be
> concrete at compile time....unless you are talking about assembling a
> string to pass to EXECUTE STATEMENT, of course. If not, then what
> evidence do you have that it works? I'd be worried to know that the
> parser allowed it...
>
> ./heLen
>
>