Dear All

I am making a stored procedure that will generate a list of employees,
searching by DEPT, EMPID, JOBGROUP... or whatever else is specified as
an INPUT parameter. I created the following but the CASE section causes
it to fail miserably (also consider the commented out section). Is this
the proper way to go about it?

Examples of how I envisage to use the procedure:

SELECT * FROM LISTEMPLOYEES(2, 5); //list of all employees in dept 5
SELECT * FROM LISTEMPLOYEES(3, 1); //list of all employees in JobGroup 1

... and here is the procedure:

CREATE PROCEDURE LISTEMPLOYEES (
REFTYPE SMALLINT, /*
REFNO BIGINT)
RETURNS (
EMPID BIGINT,
DESCR VARCHAR (30) CHARACTER SET WIN1252,
UNITS FLOAT,
BASEAMT DOUBLE PRECISION,
TOCALC SMALLINT)
AS
DECLARE VARIABLE EMPID BIGINT;
DECLARE VARIABLE DEPTID SMALLINT;
DECLARE VARIABLE PAYMETHOD SMALLINT;
DECLARE VARIABLE HRSPERWEEK FLOAT;
DECLARE VARIABLE MAXHRSPERWEEK FLOAT;
DECLARE VARIABLE EMPTYPE SMALLINT;
DECLARE VARIABLE CONTRACTTYPE SMALLINT;
DECLARE VARIABLE PAYTYPE SMALLINT;
DECLARE VARIABLE ALLOWOT SMALLINT;
DECLARE VARIABLE PAYRATE1 FLOAT; /*BASIC PAY*/
DECLARE VARIABLE PAYRATE2 FLOAT; /*TIME AND A HALF*/
DECLARE VARIABLE PAYRATE3 FLOAT; /*DOUBLE TIME*/
DECLARE VARIABLE PAYRATE4 FLOAT; /*SPECIAL RATE*/
DECLARE VARIABLE ISOFFSITEWORKER SMALLINT;
DECLARE VARIABLE ISSHIFTWORKER SMALLINT;
BEGIN
FOR
SELECT EMPID, DEPTID, PAYMETHOD, HRSPERWEEK, MAXHRSPERWEEK,
EMPTYPE, CONTRACTTYPE, PAYTYPE, ALLOWOT, PAYRATE1, PAYRATE2,
PAYRATE3, PAYRATE4, ISOFFSITEWORKER, ISSHIFTWORKER FROM
EMPLOYEES E
WHERE ISACTIVE=1 AND
(SELECT CASE :REFTYPE
WHEN 2 then
DEPTID = :REFNO
WHEN 3 then
JOBGROUP = :REFNO
WHEN 4 then
EMPGROUP = :REFNO
END
FROM EMPLOYEES)
/*CASE REFTYPE
WHEN 2 THEN
AND DEPTID= :REFNO
WHEN 3 THEN
AND DEPTID= :REFNO
WHEN 4 THEN
AND PID= :REFNO
WHEN 5 THEN
AND JOBGROUP= :REFNO
END*/

INTO :EMPPID, :DEPTID, :PAYMETHOD, :HRSPERWEEK, :MAXHRSPERWEEK,
:EMPTYPE, :CONTRACTTYPE, :PAYTYPE, :ALLOWOT, :PAYRATE1, :PAYRATE2,
:PAYRATE3, :PAYRATE4, :ISOFFSITEWORKER, :ISSHIFTWORKER
DO
BEGIN
/* The BODY is just a dummy for now */
EMPID = PID;
DESCR = 'OUTPUT';
UNITS = 0.0;
BASEAMT = 0.0;
TOCALC = 1;
SUSPEND;
END
END

Thank you in advance.