When building a procedure, I want to be able to pass a parameter or pass a NULL if I don’t want to search by a parameter. Some people will tell you to accomplish this that you should write a dynamic SQL statement so you can add or remove parameters as needed. I avoid Dynamic SQL wherever possible and this is a situation where Dynamic SQL is not needed. Instead, I can use the NVL function. More NULL fun here.

Try the following example.

create table tbl_locations (
 location_id integer,
 city varchar2(50),
 state varchar2(2),
 zip_code varchar2(10)
 );

insert into tbl_locations values (1,'ORLANDO','FL', '32801');
insert into tbl_locations values (2,'ORLANDO','FL', '32802');
insert into tbl_locations values (3,'ORLANDO','FL', '32803');
insert into tbl_locations values (4,'ORLANDO','FL', '32804');
insert into tbl_locations values (5,'ORLANDO','FL', '32805');
insert into tbl_locations values (6,'ORLANDO','FL', '32806');
insert into tbl_locations values (7,'LEESBURG','FL', '34748');
insert into tbl_locations values (8,'LEESBURG','FL', '34749');
insert into tbl_locations values (9,'LEESBURG','FL', '34788');
insert into tbl_locations values (10,'LEESBURG','FL', '34789');
insert into tbl_locations values (11,'ATLANTA','GA', '30301');
insert into tbl_locations values (12,'ATLANTA','GA', '30302');
insert into tbl_locations values (13,'ATLANTA','GA', '30303');
insert into tbl_locations values (14,'ATLANTA','GA', '30304');
insert into tbl_locations values (15,'ATLANTA','GA', '30305');
insert into tbl_locations values (16,'ATLANTA','GA', '30306');
insert into tbl_locations values (17,'MARIETTA','GA', '30006');
insert into tbl_locations values (18,'MARIETTA','GA', '30007');
insert into tbl_locations values (19,'MARIETTA','GA', '30008');

I want a procedure that will let me select State and Zip Code. If I leave Zip Code null, it should return all records in that State. If I enter a Zip Code and no State, it should return that Zip Code’s information. It shouldn’t bomb because I have a null State.

In the following procedure, NVL is used in the WHERE clause. Each row is evaluated according to the WHERE clause, determining if the conditions listed are true for that row.

create or replace procedure prc_search_locations (
  results OUT sys_refcursor, 
  p_state IN varchar2, 
  p_zipcode IN varchar2)
is
begin

 open results for
 select city, state, zip_code
 from tbl_locations
 where nvl(p_state, state) = state
 and nvl(p_zipcode, zip_code) = zip_code;

end;
/

If p_state is not null, it is returned by the NVL function and is then compared to the state column. Substituting “GA” for p_state, this is how it would look at runtime. The first value is the p_state parameter, the second value is the value found in the row. If the row is an “FL” row, it would look like the second example because the second value is the value found in the row. It comes out “GA” either way because we passed a value for p_state.

where nvl(p_state, state) = state

SQL> select NVL('GA','GA') from dual;

NV
--
GA

SQL> select NVL('GA','FL') from dual;

NV
--
GA

Alternatively, if p_zipcode is left null, then we expect all zip codes to be returned. Substituting null for p_zipcode, this is how it would look at runtime. Comparing row by row to see if the condition evaluates to true, when it happens upon the row where zip_code = “30301” it will find “30301” on both sides because the parameter is null so it is passing the second value, which comes from the table data.

and nvl(p_zipcode, zip_code) = zip_code;

SQL> select NVL(null, '30301') from dual;

NVL(N
-----
30301

The following is how the code was executed. p_state = ‘GA’ so for every row where state = p_state, the row is returned.

SQL> var r refcursor;
SQL> exec prc_search_locations (:r, p_state => 'GA', p_zipcode => null);

PL/SQL procedure successfully completed.

SQL> print r;

CITY       ST ZIP_CODE
---------- -- ----------
ATLANTA    GA 30301
ATLANTA    GA 30302
ATLANTA    GA 30303
ATLANTA    GA 30304
ATLANTA    GA 30305
ATLANTA    GA 30306
MARIETTA   GA 30006
MARIETTA   GA 30007
MARIETTA   GA 30008

9 rows selected.

If p_state is left null and p_zipcode is populated, we expect states to not be a restriction on which rows are returned, only the zip code.

SQL> var r refcursor;
SQL> exec prc_search_locations (:r, p_state => null, p_zipcode => '30301');

PL/SQL procedure successfully completed.

SQL> print r;

CITY     ST ZIP_CODE
-------- -- ----------
ATLANTA  GA 30301