CODEWITHSUNDEEP
oracle9i
Sony Mathew
Sony Mathew

Reputation: 57

check the null values for input parameters with oracle stored procedure

I am preparing stored procedure with oracle .I m using multiple inner joins with the same table where an input parameter value is checked within each inner join. I want to eliminate particular inner join if the input parameter is null

Upvotes: 0

Views: 11441

Answers (1)

Tony Andrews
Tony Andrews

Reputation: 132670

You would need to use dynamic SQL to construct the appropriate query for the parameters like this:

PROCEDURE myproc (p1 VARCHAR2) IS
   l_sql LONG;
   l_cursor SYS_REFCURSOR;
BEGIN
   l_sql := 'SELECT a, b, c FROM table1';
   IF p1 IS NOT NULL THEN
      l_sql := l_sql || ' JOIN table2 ON table2.x = table1.x';
   END IF;
   l_sql := l_sql || ' WHERE table1.y = :bind1';
   OPEN l_cursor FOR l_sql USING 123;
   ...
END;

Upvotes: 3

Related Questions