asked on

Dynamic SQL Candidate: Inefficient, logic-ridden SQL Statement

Hey Techies--

It has been some time since I've been in the DB2 world. Now that I'm back, I'm taking with me some ideas from MS Sql Server. Please review the core statement I have--which works but is inefficient. This has been placed in a stored procedure--so the variables supplied will be populated by the calling client--and yes, they could be blank individually and in combination (different meaning depending on which), Is dynamic SQL a possibility? If it is, please instruct me---I will then be able to use this as a point of reference. Any help would be appreciated.

CREATE PROCEDURE PRDPGMLIB.GETEMPBYJOBTYPE01(
                    IN @EMPIDIN VARCHAR(20),
                    IN @COMPANY VARCHAR(20),
                    IN @ACTIVE VARCHAR(20),
                    IN @LOCATION VARCHAR(20),
                    IN @OPERATIONALAREA VARCHAR(20),
                    IN @JOBCODE VARCHAR(20),
                    IN @JOBTYPE VARCHAR(20),
    				IN @NAME VARCHAR(20)
                    ) 
	DYNAMIC RESULT SETS 1 
	LANGUAGE SQL 
	SPECIFIC PRDPGMLIB.GETEMPBYJOBTYPE01 
	NOT DETERMINISTIC  
	MODIFIES SQL DATA 
	CALLED ON NULL INPUT 
	SET OPTION  ALWBLK = *READ , 
	ALWCPYDTA = *OPTIMIZE , 
	COMMIT = *NONE , 
	DECRESULT = (31, 31, 00) , 
	DFTRDBCOL = *NONE , 
	DYNDFTCOL = *NO , 
	DYNUSRPRF = *OWNER , 
	USRPRF = *OWNER,
	SRTSEQ = *HEX   

BEGIN 

DECLARE NOT_FOUND CONDITION FOR SQLSTATE '02000' ; 
DECLARE EOF_COND CHAR(1)  DEFAULT '0' ;

DECLARE FN01 VARCHAR(20);
DECLARE FN02 VARCHAR(20);
DECLARE FN03 VARCHAR(20);
DECLARE LN01 VARCHAR(20);
DECLARE LN02 VARCHAR(20);
DECLARE LN03 VARCHAR(20);
DECLARE CN01 VARCHAR(20);
DECLARE CN02 VARCHAR(20);
DECLARE CN03 VARCHAR(20);

 
DECLARE CSR1 CURSOR FOR

SELECT pren  as  empid, 
           prer  as  company, 
           prfnm as  firstname, 
           prcomn as commonname, 
           prmnm as  middlename, 
           prlnm as  lastname, 
           prl04 as  location, 
           PRDPGMLIB.FIELDORCORP(prl01) AS operationalarea,
           prtede as  termdate, 
           CASE
              WHEN prtedh = 0 THEN '1'
              ELSE '0'
            END AS isactive,
          prstat as status,
          prjob1 as jobcode, 
          prusr3 as jobtype,
          prpos   AS positioncode,
          PRDPGMLIB.POSITIONTITLE(prer, prpos) AS positiontitle, 
          prdohe  AS hiredate, 
          prrehe  AS rehiredate, 
          prpos   AS hirecode, 
          prssn   AS ssn, 
          prdobe  AS dateofbirth, 
          preth   AS ethnic,
          PRDPGMLIB.ETHNICDESC(prer,preth) AS ethnicdesc, 
          prsex   AS sex, 
          prstr1  AS addressline1,
          prstr2  AS addressline2, 
          prcty1  AS city, 
          prsta1  AS state, 
          przip1  AS zipcode,
          preml1  AS email1,
          preml2  AS email2,
          prtl1   AS telephone1,
          prtl2   AS telephone2,
          prtl3   AS telephone3,
          prtl4   AS telephone4
  FROM HRDBFA.PRPMS
 WHERE 
         ((@EMPIDIN <> ' ' AND pren=@EMPIDIN)
      OR (@EMPIDIN = ' '))
      AND 
           ((@COMPANY <> ' ' AND prer=@COMPANY)
         OR(@COMPANY = ' '))
      AND 
            ((@ACTIVE = '0' AND prtedh>0)
          OR(@ACTIVE = '1' AND prtedh=0)
          OR(@ACTIVE = ' '))
      AND 
           ((@LOCATION <> ' ' AND prL04=@LOCATION)
         OR(@LOCATION = ' '))
      AND 
           ((@OPERATIONALAREA <> ' ' AND PRDPGMLIB.FIELDORCORP(prL01)=@OPERATIONALAREA)
         OR(@OPERATIONALAREA = ' '))
      AND 
           ((@JOBCODE <> ' ' AND prjob1=@JOBCODE)
         OR(@JOBCODE = ' '))
      AND 
           ((@JOBTYPE <> ' ' AND prusr3=@JOBTYPE)
         OR(@JOBTYPE = ' '))
      AND 
         ((@NAME <> ' ' AND (prfnm LIKE FN01
					 OR prfnm LIKE FN02
					 OR prfnm LIKE FN03
                     OR prlnm LIKE LN01
					 OR prlnm LIKE LN02
					 OR prlnm LIKE LN03
			         OR prcomn LIKE CN01
					 OR prcomn LIKE CN02
					 OR prcomn LIKE CN03))
         OR(@NAME = ' '))
;



SET    @EMPIDIN = TRIM(@EMPIDIN),
       @COMPANY = TRIM(UPPER(@COMPANY)),
       @ACTIVE = TRIM(@ACTIVE),
       @LOCATION = TRIM(UPPER(@LOCATION)),
       @OPERATIONALAREA = TRIM(UPPER(@OPERATIONALAREA)),
       @JOBCODE = TRIM(UPPER(@JOBCODE)),
       @JOBTYPE = TRIM(UPPER(@JOBTYPE)),
       FN01 = '%' || TRIM(UPPER(@NAME)) || '%',
       FN02 = '%' || TRIM(UPPER(@NAME)),
       FN03 = TRIM(UPPER(@NAME)) || '%',
       LN01 = '%' || TRIM(UPPER(@NAME)) || '%',
       LN02 = '%' || TRIM(UPPER(@NAME)),
       LN03 = TRIM(UPPER(@NAME)) || '%',
       CN01 = '%' || TRIM(UPPER(@NAME)) || '%',
       CN02 = '%' || TRIM(UPPER(@NAME)),
       CN03 = TRIM(UPPER(@NAME)) || '%'
;



OPEN CSR1;
RETURN;
CLOSE CSR1; 

  
END }

Open in new window

ASKER CERTIFIED SOLUTION