Changing IF ELSE to CASE

I have the following block in my Stored procedure. It is working as expected. But I want to chan开发者_如何学Pythonge it to CASE so that I can add more conditions later as reqired.

IF parm_status='ALL' THEN 
    IF op_type='CREDIT' THEN
        set v_wherestr =concat( v_wherestr, " and (tran.operation='CREDIT')");
    ELSEIF op_type='UNCREDIT' THEN
    set v_wherestr = concat( v_wherestr, " and (tran.operation='CREDIT' OR tran.operation='UNCREDIT')");
    END IF;

...
...

END IF;

Basically, it would be:

IF parm_status='ALL' THEN 
    CASE op_type
       WHEN 'CREDIT' THEN set v_wherestr =concat( v_wherestr, " and (tran.operation='CREDIT')");
       WHEN 'UNCREDIT' THEN set v_wherestr = concat( v_wherestr, " and (tran.operation='CREDIT' OR tran.operation='UNCREDIT')");
    END;

...
...

END IF;

http://www.java2s.com/Code/SQL/Flow-Control/UseCASEWHENforstringvalue.htm

This keeps your original structure but replaces the IF THEN statements with a CASE statement. However, I would look at your actual query to see if maybe there is a more optimal way of accomplishing what you want to do. Even though this is a stored procedure, the execution plan will be a mess most likely. You might want to play around with multiple queries combined through UNION ALL or something else in order to make this more optimized. I'm sorry I can't help you further here but it really depends on your overall query and your data structure.

Try the following:

IF parm_status='ALL' THEN 
    set v_wherestr = 
        CASE op_type
           WHEN 'CREDIT' THEN concat( v_wherestr, " and (tran.operation='CREDIT')");
           WHEN 'UNCREDIT' THEN concat( v_wherestr, " and (tran.operation='CREDIT' OR tran.operation='UNCREDIT')");
        END;

... ...

END IF;

or even (this is less flexible):

IF parm_status='ALL' THEN 
    set v_wherestr = concat( v_wherestr, 
        CASE op_type
           WHEN 'CREDIT' THEN " and (tran.operation='CREDIT')"
           WHEN 'UNCREDIT' THEN " and (tran.operation='CREDIT' OR tran.operation='UNCREDIT')"
        END;
    );
... ...

END IF;