This is a current working SP which I have tried to convert to a CASE
version as a test, with no success. I think its more elegant than the
elseif constructs.
System is W2K, Firebird 1.5 Release Candidate 7, database dialect 1,
IBExpert 2004.04.01

The CASE statement itself works in an Sql editor window as per:-
select
weigh_code,
tonnes,
case when (tonnes<20) then 'light' when (tonnes>20) then 'heavy' end
from weigh
345 19.78 light
378 22.56 heavy
...
The error I get is this, no matter how I change the flow:-

Invalid token.
Dynamic SQL Error.
SQL error code = -104.
Token unknown - line 20, char 3.
case.



CREATE PROCEDURE MAINTAIN_PRODUCTS (
PRODCODE INTEGER,
PRODNAME CHAR(30) CHARACTER SET NONE,
ZEROASH CHAR(1) CHARACTER SET NONE,
ZEROWATER CHAR(1) CHARACTER SET NONE,
ACTIONTYPE INTEGER)
AS
begin
if (ActionType=1) then /* Update */
begin
update Product
set Prod_Name=:ProdName,Zero_Ash=:ZeroAsh,Zero_Water=:ZeroWater
where Prod_Code=:ProdCode;
end
else if (ActionType=2) then /* Insert */
begin
insert into Product(Prod_Code,Prod_Name,Zero_Ash,Zero_Water)
values (:ProdCode,:ProdName,:ZeroAsh,:ZeroWater);
end
else if (ActionType=3) then /* Delete */
begin
delete from Product
where Prod_Code=:ProdCode;
end
when SQLCode -803 Do
Exception insertException;/* Already On File */
end

Any comments anybody? Should CASE statements work inside the FB stored procs?

Alan J Davies
Aldis
Tel: +44(0)1926 842069
Fax: +44(0)1926 843755


--
Outgoing mail is certified Virus Free.
Checked by AVG Anti-Virus (http://www.grisoft.com).
Version: 7.0.245 / Virus Database: 263.0.0 - Release Date: 02/06/2004