Figure 2: RPG Program Used as External Stored Procedure Program

     H Bnddir('R2KBNDDIR':'QC2LE') Actgrp(*CALLER)
     FCcstate   IF   E           K Disk
     FCcstatelawIF   E           K Disk

      *------ External procedures
      *------ Gui tasks service program
      /Include Source,Guierrorpr

      *------ Internal procedures
      *------ This Program
      *------ Add Entry
     D AddEntry        PR

      *------ Set results
     D SetResults      PR

      *------ Constants
     D No              C                   'N'
     D Yes             C                   'Y'

      *------ Workfields
     D Country         S              3    Inz('USA')
     D I               S              5  0 Inz

      *------ Data structure for results set
     D SI              DS                  Occurs(50)
     D  sp_State                      2
     D  sp_Desc                      30
     D  sp_Statue                     8
     D  sp_Comm1                     70
     D  sp_Comm2                     70
     D  sp_Comm3                     70
     D  sp_Comm4                     70
     D  sp_Comm5                     70
     D  sp_Comm6                     70
     D  sp_Special1                  70
     D  sp_Special2                  70
     D  sp_Special3                  70
     D  sp_Special4                  70
     D  sp_Special5                  70
     D  Sp_Special6                  70

      *----------------------------------------------------------
      *  Mainline Calculations                                  *
      *----------------------------------------------------------
      /Free
                  // Clear data structure, index, output parameters
       Clear *All SI;
       Clear I;
       Reset Country;

                  // Get state information
       Setll  Country  Ccstate;
       Dou %Eof(Ccstate);
         Reade  Country Ccstate;
         If %Eof(Ccstate);
           Leave;
         ENDIF;

         AddEntry();
       ENDDO;

                  // Set results set
       SetResults();

                  // Return
       Return;
      /End-Free

      *----------------------------------------------------------
      * Add Entry                                               *
      *----------------------------------------------------------
     P AddEntry        B

      *------ Constants
     D IndexError      C                   'Index has exceeded limits for Multi-
     D                                     ple Occurrence Data Structure'

      *------ Workfields
     D Special         S             70    Inz Dim(6)
     D StatueOfLimit   S              8    Inz
     D Years           S              2    Inz
     D X               S              3  0 Inz

      *-----------------------------------------------------
      * Procedure Calculations                             *
      *-----------------------------------------------------
      /Free
                  // Fill in state info
       I += 1;
       Monitor;
         %Occur(SI) = I;
         On-Error *All;
           WrtGuiErr('GUI0024GSP':'ADDENTRY':'SI':IndexError);
           I = %Elem(SI);
           Return;
       Endmon;

       SI.sp_State = Tstate;
       SI.sp_Desc = Tdesc;
       SI.sp_Comm1 = Tcomm1;
       SI.sp_Comm2 = Tcomm2;
       SI.sp_Comm3 = Tcomm3;
       SI.sp_Comm4 = Tcomm4;
       SI.sp_Comm5 = Tcomm5;
       SI.sp_Comm6 = Tcomm6;
       If Tyears  > *Zeros;
         Years = %Editc(Tyears:'Z');
         StatueOfLimit = Years + ' ' + 'years';
         SI.sp_Statue = StatueOfLimit;
       Endif;

                  // Get state laws for special considerations
       Clear Special;
       Clear X;
       Setll  (Tcntry:Tstate)  Ccstatelaw;
       Dou %Eof(Ccstatelaw);
         Reade  (Tcntry:Tstate)  Ccstatelaw;
         If %Eof(Ccstatelaw);
           Leave;
         ENDIF;

         X += 1;
         Monitor;
           Special(X) = Ldesc;
           On-Error *All;
             WrtGuiErr('GUI0024GSP':'ADDENTRY':'SPECIAL':IndexError);
             Leave;
         Endmon;
       ENDDO;

       SI.sp_Special1 = Special(1);
       SI.sp_Special2 = Special(2);
       SI.sp_Special3 = Special(3);
       SI.sp_Special4 = Special(4);
       SI.sp_Special5 = Special(5);
       SI.sp_Special6 = Special(6);

                  // Return
       Return;
      /End-Free

     P AddEntry        E

      *----------------------------------------------------------
      *  Set Results Procedure                                  *
      *----------------------------------------------------------
     P SetResults      B

      *-----------------------------------------------------
      * Procedure Calculations                             *
      *-----------------------------------------------------
     C/EXEC SQL
     C+ SET RESULT SETS WITH RETURN TO CLIENT ARRAY:SI FOR:I ROWS
     C/END-EXEC

     P SetResults      E
 


SQL Stored Procedure Replaces RPG Program

CREATE PROCEDURE CCOBJ.GETSTINFO (  )
	RESULT SETS 1
	LANGUAGE SQL
	SPECIFIC CCOBJ.GETSTINFO

BEGIN
	DECLARE GLOBAL TEMPORARY TABLE SESSION . TT_STINFO (
	TT_STATE CHAR ( 2 ) ,	
	TT_PTYPE CHAR ( 4 ) ,
	TT_STATUE CHAR ( 1 ) ,
	TT_YEARS NUMERIC ( 2 , 0 ) ,
	TT_DESC CHAR ( 30 ) ,	
	TT_COMM1 CHAR ( 70 ) ,	
	TT_COMM2 CHAR ( 70 ) ,	
	TT_COMM3 CHAR ( 70 ) ,	
	TT_COMM4 CHAR ( 70 ) ,	
	TT_COMM5 CHAR ( 70 ) ,	
	TT_COMM6 CHAR ( 70 ) ,
	TT_SPECIAL1 CHAR ( 70 ) ,
	TT_SPECIAL2 CHAR ( 70 ) ,
	TT_SPECIAL3 CHAR ( 70 ) ,	
	TT_SPECIAL4 CHAR ( 70 ) ,
	TT_SPECIAL5 CHAR ( 70 ) ,
	TT_SPECIAL6 CHAR ( 70 ) )	WITH REPLACE ;
		
	INSERT INTO SESSION . TT_STINFO
	  SELECT A . TSTATE , B . STTYPECLS , B . STSTAT , B . STYEARS , A . TDESC , 
	   A . TCOMM1 , A . TCOMM2 , A . TCOMM3 , A . TCOMM4 , A . TCOMM5 , A . TCOMM6 ,	
	   COALESCE ( ( SELECT LDESC FROM CCDATA . CCSTATELAW WHERE LSTATE = A . TSTATE AND LSEQ = 1 ) , ' ' ) ,	
	   COALESCE ( ( SELECT LDESC FROM CCDATA . CCSTATELAW WHERE LSTATE = A . TSTATE AND LSEQ = 2 ) , ' ' ) ,	
	   COALESCE ( ( SELECT LDESC FROM CCDATA . CCSTATELAW WHERE LSTATE = A . TSTATE AND LSEQ = 3 ) , ' ' ) ,	
	   COALESCE ( ( SELECT LDESC FROM CCDATA . CCSTATELAW WHERE LSTATE = A . TSTATE AND LSEQ = 4 ) , ' ' ) ,	
	   COALESCE ( ( SELECT LDESC FROM CCDATA . CCSTATELAW WHERE LSTATE = A . TSTATE AND LSEQ = 5 ) , ' ' ) ,	
	   COALESCE ( ( SELECT LDESC FROM CCDATA . CCSTATELAW WHERE LSTATE = A . TSTATE AND LSEQ = 6 ) , ' ' )	
	FROM CCDATA . CCSTATE A , CCDATA . CCSTATUT B	
	WHERE A . TSTATE = B . STSTATE AND A . TCNTRY = 'USA' ;		
	
	BEGIN
	DECLARE TEMP CURSOR WITH RETURN FOR SELECT * FROM SESSION . TT_STINFO ;	
	OPEN TEMP ;	
	END ;	
END;