CODEWITHSUNDEEP
sqloracle-databaseplsql
CoolArchTek
CoolArchTek

Reputation: 3829

Oracle SQL - Alternative for dynamic query

I have a procedure (see below) which is using dynamic query. I wanted to rewrite this procedure with out using dynamic query. How do I write the conditions below? 

PROCEDURE DemoProcedure(p_firstname  IN VARCHAR2,
                       p_lastname   IN VARCHAR2,
                       p_phone        IN VARCHAR2
                       o_Cursor     OUT t_Cursor) IS

SQLString VARCHAR2(4000);

BEGIN

    SQLString := 
        'SELECT * FROM 
        SCHEMA.TABLENAME_a A
        INNER JOIN SCHEMA.TABLENAME_b B
        ON A.ID = B.ID
        WHERE 
        A.TYPE = 1  ';

    IF p_firstname IS NOT NULL THEN
        SQLString := SQLString || ' and UPPER(A.FIRST_NAME) like UPPER( ''' || p_firstname || ''')';
    END IF;

    IF p_lastname IS NOT NULL THEN
        SQLString := SQLString || ' and UPPER(A.LAST_NAME) like UPPER( ''' || p_lastname || ''')';
    END IF;

    IF p_phone IS NOT NULL THEN
        SQLString := SQLString || ' and UPPER(A.PHONE) = ''' ||
                            p_phone || '''';
    END IF;

        SQLString := SQLString || ' order by  a.id ';

        OPEN o_Cursor FOR SQLString;

END DemoProcedure;

Upvotes: 0

Views: 912

Answers (2)

Sandeep
Sandeep

Reputation: 349

Different way writing the same SQL as @JustinCave suggested - 

OPEN o_cursor 
 FOR SELECT ...
      WHERE A.TYPE = 1
        AND upper(a.first_name) = nvl(upper(p_firstname), upper(a.first_name))
        AND upper(a.last_name)  = nvl(upper(p_lastname),upper(a.last_name))
        AND upper(a.phone)      = nvl(p_phone,upper(a.phone))
      ORDER BY a.id

Upvotes: 3

Justin Cave
Justin Cave

Reputation: 231651

It looks like you just want

OPEN o_cursor 
 FOR SELECT ...
      WHERE A.TYPE = 1
        AND (p_firstname IS NULL or upper(a.first_name) = upper(p_firstname))
        AND (p_lastname  IS NULL or upper(a.last_name)  = upper(p_lastname))
        AND (p_phone     IS NULL or upper(a.phone)      = p_phone)
      ORDER BY a.id

I'm not sure why you'd want to bother upper-casing a phone number-- do you have character data in a phone number?

Upvotes: 3

Related Questions