SQL Stored Procedures

Hi Guys,

Today I am writing about Stored Procedures. What are stored Procedures, How to write them, How to compile and how to call them?

Stored Procedures

Stored procedures are programs that are called from SQL. These programs can be written using the SQL procedure language, but they may also be written using RPG.

So how do you write SQL Procedure? It is simple. You just need to write SQLRPGLE with “CREATE PROCEDURE” SQL Statement

Example 1.

C/Exec SQL

C+CREATE PROCEDURE GetName 

C+ (IN ProgLang CHARACTER(10), 

C+IN Comp CHARACTER(20), 

C+INOUT FOUND INTEGER(4)) 

C+LANGUAGE SQL 

C+SELECT COUNT(*) INTO FOUND FROM TestLib/NameData 

C+WHERE TechLang = ProgLang AND Employeer = Comp

C/End-Exec

How to compile the Procedure?

You can compile the above code like any other SQLRPGLE code

How to call above procedure?

Create another RPGLE code as below

D GetName        PI

     D  ProgLang                     10A

     D  COMP                         20A

     D  Found                         4 

.

.

.

.

    Call GetName( :EMPID, :Comp, :Found);

Example 2.

c/EXEC SQL                                                

c+ Create procedure OrderSumm(                            

c+   in    partnerID            NUMERIC(5,0),             

c+   in    storeNumber          NUMERIC(7,0) )            

C+ LANGUAGE RPGLE NOT DETERMINISTIC                       

c+ READS SQL DATA                                         

c+ EXTERNAL NAME EXTPGM01                                 

c+ PARAMETER STYLE GENERAL                                

c/END-EXEC                      

Above code will be called as below

Create another RPGLE code as below

D OrderSumm        PI

     D  PartnerID                     5

     D  StoreNumber                   7

.

.

.

.

    Call OrderSumm( :PartnerId, :StoreNumber);

In example 2 when OrderSumm is called, it in returns call further a new program “EXTPGM01”

Do write me @ iSeriesblogs@gmail.com for any query or you can simply post comments below.

Happy Learning