Oracle DB - How to set IF statement in AND operator?

Question

I want to set IF statement before execute AND operator.

For Example:

PROCEDURE STORE_SIGNLOOP_APPROVER
(
    userID     in  varchar2
    isManager  in varchar2
    result   out ref_cursor
) 
AS

BEGIN
    SELECT user_role
    FROM users
    WHERE user_id = userID
    AND **IF isManager = 'true'**  user_id IN (SELECT user id from managers)
END;

why should you precompute the "ismanager"? you can check it inside you proc. If a user is also inside the manager-table it is an manager. why are u using varchar for a true/fale value? — Patrick Artner, Dec 03 '17 at 08:36
Hi, its only simple example to explain my purpose. you right it's not very good one ;) — Ran Alcobi, Dec 03 '17 at 08:40
For this "example" I would simply join the managers to the users - done. if the user is no manager, no result.No idea why use a procedure as well.: https://stackoverflow.com/a/25419698/7505395 — Patrick Artner, Dec 03 '17 at 08:46

score 1 · Answer 1 · answered Dec 03 '17 at 08:36

You could use:

select user_role
fron users
where user_id = userID
  AND (((user_id in (select user_id from managers) AND isManager = 'true'))
       OR isManager = 'false'
      );

Note: Instead of passing 'true/false' as string literal you could use '0/1' values.

Oracle DB - How to set IF statement in AND operator?

1 Answers1