Dynamic SQL-Command in PL/SQL

From: COAST <coast_at_cedes.com>
Date: Fri, 25 Feb 2005 11:49:59 +0100
Message-ID: <421f02db$0$3680$4d4ef98e@read.news.ch.uu.net>

Hi NG

I've got the following sql-statement:

PROCEDURE MY_PROC(myVar IN NUMBER,) IS
BEGIN   DECLARE   BEGIN   FOR myAttributes IN ( select myAttribute into myAttributeResult from myTable

                                             where myNumber = myVar;)

  LOOP     doSomething();

  END LOOP;   END;
END; Now I've got the problem if myVar is NULL then I want to have ALL entries. So the I need:

PROCEDURE MY_PROC(myVar IN NUMBER,) IS
BEGIN   DECLARE   BEGIN   IF myVar is not null THEN
    FOR myAttributes IN ( select myAttribute into myAttributeResult from myTable

                                             where myNumber = myVar;)

    LOOP       doSomething();

    END LOOP;
  ELSE
    FOR myAttributes IN ( select myAttribute into myAttributeResult from myTable)

    LOOP       doSomething();

    END LOOP;
  END IF;   END;
END; That means I have to write twice the same!! Is there a way to write this case with one sql-statement.

Specially, if you have more than one argument in the proc (which you have to test on NULL), your code will multiply and the code will not be very handsom if you have any changes.

Thanks for any hint
Peter Received on Fri Feb 25 2005 - 04:49:59 CST