Re: Stored Procedure library list

I have an sql stored procedure that runs against some as400 files and
returns a result set,
it looks like it's working the way I would like, but I also have a need to
run this against different companies with different library lists

I have an as400 clp I can run that will set the library lists for me, but
I don't know how to call the program from a sql stored procedure, and
haven't been able to find an answer searching thru the internet, manuals,
forums.

This is what it looks like as created from generate sql in ops nav

By knowing the company as the IN parm, I can determine the library list by
calling program SETCO1CL with the company as a parameter. I just don't
know how to call it, or where to fit it into the procedure without
generating errors with runsqlstm.

DROP SPECIFIC PROCEDURE CRPRDLIBTS/STR005SP ;

SET PATH "QSYS","QSYS2","SYSPROC","SYSIBMADM","DRLPGMR" ;

CREATE PROCEDURE CRPRDLIBTS/STR005SP (
IN COCD CHAR(2) ,
IN LNAM CHAR(35) )
DYNAMIC RESULT SETS 1
LANGUAGE SQL
SPECIFIC CRPRDLIBTS/STR005SP
NOT DETERMINISTIC
READS SQL DATA
CALLED ON NULL INPUT
SET OPTION ALWBLK = *ALLREAD ,
ALWCPYDTA = *OPTIMIZE ,
COMMIT = *NONE ,
LANGUAGE SQL
SPECIFIC CRPRDLIBTS/STR005SP
NOT DETERMINISTIC
READS SQL DATA
CALLED ON NULL INPUT
SET OPTION ALWBLK = *ALLREAD ,
ALWCPYDTA = *OPTIMIZE ,
COMMIT = *NONE ,
CLOSQLCSR = *ENDMOD ,
DECRESULT = (31, 31, 00) ,
DFTRDBCOL = *NONE ,
DLYPRP = *NO ,
DYNDFTCOL = *NO ,
DYNUSRPRF = *USER ,
RDBCNNMTH = *RUW ,
SRTSEQ = *HEX

BEGIN

DECLARE WRKDAT NUMERIC (8,0);
SET WRKDAT = (YEAR(CURRENT DATE)*10000) + (MONTH(CURRENT
DATE)*100) + (DAY(CURRENT DATE));
BEGIN
DECLARE C1 CURSOR FOR

SELECT PCCOCD , PCAGNT , PCPOLM ,PCCOVC, PCSDES, PCEFDT ,PCEXDT
,INLNAM , INFNAM , AGNAME

FROM SPFILES/COVMST
INNER JOIN SPFILES/INSNAM ON INCOCD = PCCOCD AND INAGNT = PCAGNT
AND INPOLM = PCPOLM
AND ININS# = PCINS#
JOIN SPFILES/ACCTMST ON INCOCD = AGCOCD AND INAGNT = AGACCT

WHERE PCCOCD = COCD AND INLNAM = LNAM
AND PCPOLM <> ' '
AND PCCOVC >= 100 AND PCCOVC < 300 AND PCCNDT = 00000000
AND PCEXDT >= WRKDAT
ORDER BY PCCOCD , PCPOLM , INLNAM ;

OPEN C1 ;

END;
END;

COMMENT ON SPECIFIC PROCEDURE CRPRDLIBTS/STR005SP
IS 'SELECT INSURED BY LAST NAME';

Darrell Lee
Information Technology
Extension 17127