CODEWITHSUNDEEP
sqlstored-proceduresplsql
Mihnea Mihai
Mihnea Mihai

Reputation: 181

PL/SQL Procedure - ignore argument if null

I have a procedure that takes three arguments and then queries a table based on those arguments. Now one of them could be null and if it is, I'd like it to be ignored in the WHERE clause of the selection.

create or replace PROCEDURE Procedure1 
(
   COUNTRY IN VARCHAR2, MAKE IN VARCHAR2, SERIAL IN number
) AS

BEGIN

DECLARE
  CURSOR c1 IS  select v.ID from vehicle v   
                where v.country = COUNTRY AND 
                      v.make = MAKE AND 
                      ((SERIAL IS NOT NULL AND v.serial = SERIAL) OR 1);


BEGIN 

  FOR e_rec IN c1 LOOP

    DBMS_OUTPUT.PUT_LINE(e_rec.id);

  END LOOP;

END;
END Procedure1;

I tried something like this but it doesn't work.

Upvotes: 1

Views: 1521

Answers (2)

Sandeep
Sandeep

Reputation: 349

try this - this is same as @Miller suggested just using NVL function

WHERE v.country = nvl(COUNTRY,v.country) 
AND v.make = nvl(MAKE,v.make)   
AND v.serial = nvl(SERIAL,v.serial)

Upvotes: 1

Miller
Miller

Reputation: 1156

you can use the condition like

WHERE v.country = COUNTRY AND 
                      v.make = MAKE AND 
                     (SERIAL IS NULL or v.serial = SERIAL)

This will also work

WHERE v.country = COUNTRY AND 
                  v.make = MAKE AND 
((SERIAL IS NOT NULL AND v.Serial = SERIAL) OR SERIAL IS NULL)

Upvotes: 2

Related Questions