Checking Null Parameters in Oracle Stored Procedure
I am trying to create an Oracle Stored Procedure in 10G for a search page.
My problem is I do not know how to do a check in the where clause for Null parameters being passed in.
When a Null parameter is passed in then the user doesnt want to search on that field and so all records should be returned.
This code below will work correctly BUT doesnt include the check for Null parameters.
create or replace package body searchRecords is
procedure searchRecord(o_rpt out resultset, i_firstname in varchar2, i_lastname in varchar2) is
open o_rpt for
select * from users where users.firstname = i_firstname and users.lastname = i_lastname;
I am able to do this in SQL Server e.g.
Where (users.firstname =
CASE WHEN (i_firstname IS NULL) THEN
Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.