Case construct in stored procedure

[Bart Pietercil]

On 26-sep-07, at 11:42, Ivan Smahin wrote:

> 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.

Yes clear ( and it was marked this way in the WIKI page, read it  
without processing it. Maybe two examples in the WIKI would prevent  
this sort of waste of time)

Thanks again Ivan

Bart