How to create and Use SQL Stored Procedures?

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);