How to Create Stored Procedures

Stored procedures are programs written in SQL.  They are their own object, stored on the iSeries db and can accept parameters. They are programs with their own variables, flow control and can be called by any other application. Their benefit is in being able to stack a bunch of statements together and so improve performance, reduce network traffic and make your life simple! Why have multiple db statements executing when you can issue one that does it all! It encourages separation of the data from logic and forces you to be modular. Begone Spaghetti!

They also help security; especially useful in distributed  apps (client/server)  so that the code that updates the db is hidden in the stored procedure and not visible at the front end. Finally they are a super simple way to do a remote call.

You create a stored procedure using iSeries Navigator or directly through your 5250 screen. I'll go over the 5250 way since it is more verbose than the GUI.
To create our procedure we will use  the CREATE PROCEDURE command in SQL -called an SQL stored procedure. You can also create them in RPG - an external procedure. 

There are 4 main parts to the creation:

1. The NAME of the procedure

 CREATE PROCEDURE  MyProcedureName

2. Parameters I have an input parm of 10 chars and an I/O parm of type integer

( IN REQKEY CHARACTER(10), INOUT RETVAL INTEGER )

 3. Procedure properties The language will always be SQL

 LANGUAGE SQL 

 3. The procedure code

BEGIN  DECLARE CURS1 CURSOR FOR     SELECT CUSTNAME + 1 FROM MYLIB. MYTABLE      WHERE TELNO = REQKEY FOR UPDATE OF CUSTNAME;
   Here's an example which creates a stored procedure in SQL for
updating a table called MYTABLE.

This has two fields; CustName and TelNo.  When called with a
customer name,  it returns the phone number.

 CREATE PROCEDURE MYLIB.STOREDPROC         

(IN REQKEY CHARACTER(10),     INOUT RETVAL INTEGER )
RESULT SETS  0
LANGUAGE SQL  
NOT DETERMINISTIC
/*Start of Procedure Code*/
  BEGIN  /* Code always begins with 'Begin' */
  DECLARE CURS1 CURSOR FOR
    SELECT CUSTNAME + 1 FROM MYLIB. MYTABLE
    WHERE TELNO = REQKEY
FOR UPDATE OF CUSTNAME;   OPEN CURS1  ;

FETCH CURS1  INTO RETVAL ;

UPDATE MYLIB. MYTABLE SET CUSTNAME= RETVAL
    WHERE CURRENT OF CURS1 ;
 END

I'll cover calling these from .NET in more detail soon. 

In the meantime, create yourself a simple Stored 

procedure and try calling it. Then take one of  your

 existing programs and replace your inline SQL 

replacing it with a stored procedure.