Search code examples
sqloracle-databaseconnect-by

How to make CONNECT BY parameter optional

I have a procedure that uses Connect By

SELECT <lots of fields>
FROM Group g
  <joins>
WHERE <where>
CONNECT BY PRIOR g.ID = g.ParentID
START WITH g.ID = 1337
ORDER SIBLINGS BY g.Name
;

The number 1337 is a parameter on this procedure, if this value is 0 I would like to ignore the connect by code and execute everything else.

How can I handle this?

Solution

  • The most obvious answer is to test for the exception value in the connect by clause:

    SELECT <lots of fields>
FROM Group g
  <joins>
WHERE <where>
CONNECT BY PRIOR g.ID = g.ParentID and :param <> 0
START WITH g.ID = :param or :param = 0
ORDER SIBLINGS BY g.Name;