Re: Stored Procedure library list

[Charles Wilt <charles.wilt@xxxxxxxxx>]

This question doesn't seem to be related to RPG.

Since there isn't an SQL forum (nudge, nudge David...) it should be
posted in MIDRANGE-L.

In the meantime, here's a hint...every *PGM object on IBM i is
CALLable as a stored procedure.

Charles

On Wed, May 20, 2009 at 2:12 PM,  <DLee@xxxxxxxx> wrote:
> 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.