Case construct in stored procedure

[Ivan Smahin]

Hello Bart,

Wednesday, September 26, 2007, 12:11:03 PM, you wrote:

> Hi List,

> could somebody explain to me how the case statement in this stored  
> procedure could be acceptable to Valentina. It is now refused with:

> Error: Kernel error: 0x71000. line 11:24: expecting ')', found '='


> create or replace procedure spLogin(in vc_logon varchar,IN  
> vc_password varchar,OUT userid long)
> begin
>         
>         set userid = select recid from tbl_users where user_logonname =
> vc_logon AND user_password = vc_password;
>         IF userid IS NOT null then
>                 SET @user_id = userid;
>                 update tbl_users SET last_login_dt = now() WHERE recid = @user_id;
>         else
>         set users_in_table = SELECT count(recid) FROM TBL_USERS;
>         CASE TRUE
>                 when (users_in_table = 0) then
>                         raise 50002,'no users in table';
>                 when (users_in_table > 0) then
>                         raise 50001, 'invalid login';
>         end case
>         END if
> exception
>         call cm_CoreErrorHandler();
> end

> Do I have to use IF construct for this kind of logic ?

There are two forms of 'CASE' statement.

The first is:
..
CASE inValue
     WHEN 1 THEN INSERT INTO t1 VALUES( 1 );
     WHEN 2 THEN INSERT INTO t1 VALUES( 2 );
ELSE
    INSERT INTO t1 VALUES( 3 );
END CASE
...

The second one is:
...
CASE
    WHEN inValue = 1 THEN   INSERT INTO t1 VALUES( 1 );
    WHEN (inValue = 2) THEN INSERT INTO t1 VALUES( 2 );
ELSE
    INSERT INTO t1 VALUES( 3 );
END CASE
...


Seems second case is for your.

-- 
Best regards,
Ivan Smahin 
Senior Software Engineer
Paradigma Software, Inc
Valentina - The Ultra-Fast Database
http://www.valentina-db.com