SQL & PL/SQL

Hi,

I am passing two parameters to a stored procedure and i would pass any one of the parameter or both or null values. Stored procedure should generate the dynamic SQL based on the parameter value and return the result sets.

The basic query will look like this.

Select Emp_ID, EMp_Name from employee where Emp_ID = '10' and EMp_Name = null;
Select Emp_ID, EMp_Name from employee where Emp_ID = null and EMp_Name like '%a%';
Select Emp_ID, EMp_Name from employee where Emp_ID = '10' and EMp_Name like '%a%';
Select Emp_ID, EMp_Name from employee where Emp_ID = null and EMp_Name = null;

I consolidated the above query in a single store procedure using case statements as shown below. (As per the instructions given to me, i should use CASE statement). This is not the correct SQL statements. It shows some compilation erros.


CREATE OR REPLACE PACKAGE BODY "PKG_Emp" AS
FUNCTION GetEmployeeDetails(EmpID Employee.EMP_ID%Type,EmpName Employee.Emp_Name%Type)
RETURN GetEmployeeDetails_REF_CURSOR
IS
u_cursor GetEmployeeDetails_REF_CURSOR;
BEGIN
Open u_cursor
FOR
Select Emp_ID, EMp_Name from employee where
case when EmpID is not null then Emp_ID = EmpID else Emp_ID is null;
case when EmpName is not null then Emp_Name like '%EmpName%' else Emp_Name is null; End CASE;
RETURN u_cursor;
END GetEmployeeDetails;
END;

I would like to know, whether the above SP will help me to get the desired result?
Any suggestions/thoughts/Alternate Approach would be greatly appreciated.