Re: Stored Procedure on iSeries.

["Alan Campin" <Alan.Campin@xxxxxxxxxxxxxxxx>]

Here is a valid stored procedure. Note the ;'s,

/*   *_> CNLLSTSPLF SRCFILE(@2/@1) SRCMBR(@3)
*/     
/*   *_> RUNSQLSTM SRCFILE(@2/@1) SRCMBR(@3) +
*/     
/*   *_>   DFTRDBCOL(*NONE) CLOSQLCSR(*ENDMOD) ERRLVL(20)
*/     
Drop Procedure MAPDBA/QACOVERLETTER ;

 

Create Procedure MAPDBA/QACOVERLETTER

  (In InCarNumber Varchar(50))

  Dynamic Result Sets 1

  Language SQL

  Not Deterministic

  Reads SQL Data

 

 Set Option DbgView = *Source, DatFmt = *ISO, DatSep = '-'

 

                                                       
 Begin                                                 
   Declare C1 Cursor With Return For                   
     Select VarChar(a.CRNUMB,50,037) As CarNumber,     
            iDate(a.CRDATE) As CarDateISO,             
            VarChar(b.VNAME,25,037) As VendorName,     
            VarChar(b.VADD1,25,037) as VendorAddress1, 
            VarChar(b.VADD2,25,037) As VendorAddress2, 
            VarChar(b.VCITY,25,037) As VendorCity,     
            VarChar(b.VSTAC,2,037) As VendorState,     
            VarChar(b.VZIPC,10,037) As VendorZip       
       From CAR a                                      
         Inner Join VENNAM b                           
           On a.CRVNUM = b.VNDNR                       
       Where a.CRNUMB = InCarNumber;                   
                                                       
   Open C1 ;                                           
 End  ;                                                
                                               
Comment On Procedure MAPDBA/QACOVERLETTER      
 Is 'Get Vendor Data for Cover Letter.' ;