Link to home
Start Free TrialLog in
Avatar of Rosscowie
Rosscowie

asked on

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
  begin
  open o_rpt for
    select * from users where users.firstname = i_firstname and users.lastname = i_lastname;
  end;
 
end searchRecords;



I am able to do this in SQL Server e.g.

Where (users.firstname =
CASE WHEN (i_firstname IS NULL) THEN
          users.firstname
    ELSE
         i_firstname
END)
Avatar of gatorvip
gatorvip
Flag of United States of America image

this should work

Where users.firstname =
( CASE WHEN i_firstname IS NULL THEN  users.firstname
    ELSE  i_firstname
END)
Avatar of Rosscowie
Rosscowie

ASKER

Thanks. This is very much like the SQL Server equivalent.
One final part is how to check Null values in the table. In SQL Server I would normally use a IsNull function but it doesn't seem to work in Oracle.

Example from SQL Server:-

Where IsNull(users.firstname, 0) =
( CASE WHEN i_firstname IS NULL THEN  IsNull(users.firstname,0)
    ELSE  i_firstname
END)

This just check null in the table.

Is there a IsNull function in Oracle
ASKER CERTIFIED SOLUTION
Avatar of gatorvip
gatorvip
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial