• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 6968
  • Last Modified:

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)
0
Rosscowie
Asked:
Rosscowie
  • 2
1 Solution
 
gatorvipCommented:
this should work

Where users.firstname =
( CASE WHEN i_firstname IS NULL THEN  users.firstname
    ELSE  i_firstname
END)
0
 
RosscowieAuthor Commented:
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
0
 
gatorvipCommented:
The equivalent is nvl(value, default)
0

Featured Post

Hire Technology Freelancers with Gigs

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.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now