CODEWITHSUNDEEP
oracle-databaseplsql
Marlon W
Marlon W

Reputation: 107

Conditional selection in stored procedure PLSQL?

I've created a stored procedure to search for employee names, however I want it open ended so that the user can insert a first or last name, or no name at all.. just wondering how to structure the logic for this.

Basically something like below.. I want it to skip past the check for a name if it's null. Should the line below work? Or have I messed up. Thanks.

search_employe(name, type)

SELECT * FROM employee_table e
WHERE e.type = type
AND IF name is not null THEN e.FNAME = name OR e.LName = name END IF
AND {...}

Upvotes: 0

Views: 697

Answers (1)

Andrew
Andrew

Reputation: 27294

Selection doesn't need any plsql, but the original post talks about 'insert', so that may need some clarification - do you mean, pass the parameter?

SELECT * FROM employee_table e
WHERE e.type = type
AND (name is null OR e.FNAME = name OR e.LName = name)

I think it makes very little sense / very confusing to have a stored procedure where the caller passes in a name to be matched, but you are also to return any employee who has no name registered against them, and consider that a match. If you really want to return such confusing data (QA will have a field day) then it can be done:

SELECT * FROM employee_table e
WHERE e.type = type
AND 
(
    -- matching names, or the value was passed in null
    (name is null OR e.FNAME = name OR e.LName = name)
    OR
    -- value passed in, but the employee has no registered name
    (name is not null AND e.FNAME is null AND e.LName is null)
)

But I really recommend that this goes back to the drawing board to understand the requirements and integrity of the data.

Upvotes: 1

Related Questions