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
--
This is the RPG programming on the IBM i / System i (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.