CODEWITHSUNDEEP
sqloracle-databaseplsql
Kgn-web
Kgn-web

Reputation: 7555

how to apply and condition only if the parameter is not null in plsql

procedure get_documents
(pi_id in varchar2,
-- Inside Outside param
 pi_entry_pemit in varchar2,
 po_resultSet out sys_refcursor)
is
begin
OPEN po_resultSet FOR
select typ.description,
typ.attch_type,
typ.code_sp_type,
from vtr_t_doc_atch_type_master typ,
vtr_t_doc_atch_job_mapping jmap
where typ.active = 'Y'
if(pi_entry_pemit is not null)
{
  --then add this 'and' operatior condition
and jmap. inside_outside_type = pi_entry_pemit
}

So how do I add the AND operator only if the passed parameter is not NULL??

Upvotes: 1

Views: 3278

Answers (3)

Pawan Rawat
Pawan Rawat

Reputation: 525

You can use CASE statement inside where clause to check the condition like below

CREATE OR REPLACE PROCEDURE get_documents(
pi_id IN VARCHAR2,
pi_entry_pemit IN VARCHAR2,
po_resultSet OUT sys_refcursor)
IS
BEGIN
 OPEN po_resultSet FOR 
  SELECT typ.description, typ.attch_type, typ.code_sp_type
  FROM vtr_t_doc_atch_type_master typ, vtr_t_doc_atch_job_mapping jmap 
  WHERE typ.active = 'Y'
  AND jmap.inside_outside_type = CASE WHEN pi_entry_pemit is null then 
jmap.inside_outside_type else pi_entry_pemit;
END;

Upvotes: 0

Vijay Balebail
Vijay Balebail

Reputation: 185

At the very beginning of the procedure , Add a if condition to check if param is null. If it is null use the exit function to get out of procedure.

Eg. If (param is null ) then exit ;

Upvotes: 0

BJones
BJones

Reputation: 2460

Are you talking about something like this?

where typ.active = 'Y'
and ((pi_entry_pemit is not null and jmap.inside_outside_type = pi_entry_pemit) 
     OR (pi_entry_pemit is null))

Upvotes: 1

Related Questions