DF768 DF768 - 6 months ago 12
SQL Question

PL SQL Parameter List Empty

If I have a PL SQL procedure like this:

Create Or Replace Procedure get_age (first_name varchar(40), last_name varchar(50))

Begin

Select age
From Person
Where first = first_name AND last = last_name;

End;


It is not guaranteed that the user will pass in a value for the
first_name
and
last_name
variable.

How do I account for this in the Procedure above since I do not want the
first_name
or
last_name
in the
Where
clause of my query if either one of those variables do not have a value.

Answer

Your query is good as it is now, you can accept null values in your WHERE clause:

Where (first = first_name OR first_name is NULL) AND (last = last_name OR last_name in NULL);

This way the user can enter first and last names, first or last names only or neither and results will be selected as expected.