Creating procedure with dynamic conditions is failing

Question

I want to create a stored procedure where the parameters might be optional. So I tried with the below stored procedures.

create or replace PROCEDURE FETCH_CMP_CONSOLIDATED_REPORT
(
    P_STATENAME IN NVARCHAR2,
    P_CITYNAME IN NVARCHAR2,
    P_FROMDATE IN DATE,
    P_TODATE IN DATE,
    TBLOUT OUT SYS_REFCURSOR
)
AS

V_QUERY_STRING  NVARCHAR2(5000);
V_WHERE_CONDITION NVARCHAR2(5000);

BEGIN

OPEN TBLOUT FOR

V_QUERY_STRING  = 'SELECT a.changerequestid, a.changerequestnumber, a.networktype, a.statename, a.cityname, a.description, a.createdon, a.lastmodifiedon, a.lastmodifiedby,a.band,
b.sap_id, b.site_type, b.cr_category, b.latitude, b.longitude, b.approve_reject
FROM changerequests a
inner join tbl_pre_post_hoto b
on a.changerequestid = b.CHANGEREQUEST_ID';

IF P_STATENAME IS NOT NULL THEN
    V_WHERE_CONDITION = 'WHERE a.statename = P_STATENAME';
ELSIF P_CITYNAME IS NOT NULL THEN
    V_WHERE_CONDITION = 'WHERE a.cityname = P_CITYNAME';
ELSIF P_FROMDATE IS NOT NULL THEN
    V_WHERE_CONDITION = 'WHERE a.createdon = P_FROMDATE';
ELSE P_TODATE IS NOT NULL THEN
    V_WHERE_CONDITION = 'WHERE a.lastmodifiedon = P_TODATE';
END;

END FETCH_CMP_CONSOLIDATED_REPORT;

But there are some errors like

Error(27,19): PLS-00103: Encountered the symbol "=" when expecting one of the following: := . ( @ % ; The symbol ":= was inserted before "=" to continue.

How to create dynamic conditions based on the values supplied?

Answer 1

There are some errors, true.

• local variables should be VARCHAR2, not NVARCHAR2 (as you'll get "expression is of wrong type" error)
• parameters should be concatenated into where condition
  • beware of SQL injection (if there's any concern about it)
  • pay attention to strings and dates you're passing as parameters (hint: enclosing them into single quotes)
• don't forget to include space between parts of the statement
• before actually running the final statement, it is a good idea to display it to verify whether it is correct or not
• code you posted suggests that only one parameter is possible at a time; if not, you'll have to modify where condition (concatenate previous value, include the and keyword)

I don't have your tables, so I'll illustrate it on Scott's sample schema.

SQL> CREATE OR REPLACE PROCEDURE p_Fetch (p_deptno    IN     NUMBER,
  2                                       p_loc       IN     VARCHAR2,
  3                                       p_hiredate  IN     DATE,
  4                                       tblout         OUT SYS_REFCURSOR)
  5  AS
  6     v_query_string     VARCHAR2 (5000);
  7     v_where_condition  VARCHAR2 (5000);
  8  BEGIN
  9     v_query_string :=
 10           'select d.dname, e.ename, e.job '
 11        || ' from emp e join dept d on d.deptno = e.deptno ';
 12
 13     IF p_deptno IS NOT NULL
 14     THEN
 15        v_where_condition := ' where d.deptno = ' || p_deptno;
 16     ELSIF p_loc IS NOT NULL
 17     THEN
 18        v_where_condition := ' where d.loc = ' || CHR (39) || p_loc || CHR (39);
 19     ELSIF p_hiredate IS NOT NULL
 20     THEN
 21        v_where_condition :=
 22           ' where e.hiredate <= date ' || CHR (39) || p_hiredate || CHR (39);
 23     END IF;
 24
 25     OPEN tblout FOR v_query_string || ' ' || v_where_condition;
 26  END;
 27  /

Procedure created.

Let's try it.

SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'yyyy-mm-dd';

Session altered.

SQL> SET SERVEROUTPUT ON
SQL> DECLARE
  2     rc       SYS_REFCURSOR;
  3     --
  4     l_dname  dept.dname%TYPE;
  5     l_ename  emp.ename%TYPE;
  6     l_job    emp.job%TYPE;
  7  BEGIN
  8     p_fetch (p_deptno    => NULL,
  9              p_loc       => NULL,
 10              p_hiredate  => DATE '1981-05-01',
 11              tblout      => rc);
 12
 13     LOOP
 14        FETCH rc INTO l_dname, l_ename, l_job;
 15
 16        EXIT WHEN rc%NOTFOUND;
 17        DBMS_OUTPUT.put_line (l_dname || ' ' || l_ename || ' ' || l_job);
 18     END LOOP;
 19  END;
 20  /
RESEARCH JONES MANAGER
RESEARCH SMITH CLERK
SALES BLAKE MANAGER
SALES ALLEN SALESMAN
SALES WARD SALESMAN

PL/SQL procedure successfully completed.

Another test:

SQL> DECLARE
  2     rc       SYS_REFCURSOR;
  3     --
  4     l_dname  dept.dname%TYPE;
  5     l_ename  emp.ename%TYPE;
  6     l_job    emp.job%TYPE;
  7  BEGIN
  8     p_fetch (p_deptno    => NULL,
  9              p_loc       => 'NEW YORK',
 10              p_hiredate  => NULL,
 11              tblout      => rc);
 12
 13     LOOP
 14        FETCH rc INTO l_dname, l_ename, l_job;
 15
 16        EXIT WHEN rc%NOTFOUND;
 17        DBMS_OUTPUT.put_line (l_dname || ' ' || l_ename || ' ' || l_job);
 18     END LOOP;
 19  END;
 20  /
ACCOUNTING CLARK MANAGER
ACCOUNTING KING PRESIDENT
ACCOUNTING MILLER CLERK

PL/SQL procedure successfully completed.

SQL>