Oracle and optional stored procedure parameter in where clause
oracle, stored-procedures
Solution
In this case you can do:
where param1=123 and (param2 is null or param2 != some_value)
If param2 is not null - then it's true only if `param2 != some_value` - as expected If param2 is null - then it returns true no matter what `some_value` is
Problem
I have a stored procedure with an optional parameter like this ``` create or replace PROCEDURE "my_stored_procedure" (param1 int, param2 int default null) IS BEGIN [select some fields from some tables] ... ``` I need a `where` clause with a if on the default parameter (if it is set), but I don't even know if it's possible... Something like ``` where param1=123 and (if param2 is not null then some_value != param2) ``` The `select` clause it's pretty long and complex, so I'll prefer to have a "flexible" `WHERE` rather than a structure like ``` if param2 is not null then [long and complex select] where param1=123 and some_value != param2 else [long and complex select] where param1=123 ``` Is this possible?