> Should CASE statements work inside the FB stored procs?

Little clarificaion so we do not confuse others:
Firebird does support CASE expressions (and they work inside SP just fine),
Firebird does not support CASE statements.

Ivan

----- Original Message -----
From: "alan davies" <Alan.Davies@...>
To: <firebird-support@yahoogroups.com>
Sent: Friday, June 04, 2004 1:03 AM
Subject: [firebird-support] candidate for case statement in SP


> 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