AS400 SQL STORED PROCEDURES AND EXTERNAL STORED PROCEDURES EXAMPLES

A stored procedure is a subroutine available to applications accessing a relational database system. Stored procedures are actually stored in the database data dictionary. In AS400 it is stored in SYSROUTINES and SYSPARMS tables.

AS400 the stored procedures cane called from SQLRPGLE program or from STRSQL utility screen. Stored procedures may return result sets i.e. the results of a SELECT statement,or it can retun the paramters declared as OUT. Result sets can be processed using cursors, by other stored procedures, by associating a result set locator, or by applications. Stored procedures may also contain declared variables for processing data and cursors that allow it to loop through multiple rows in a table. Stored procedure languages typically include IF, WHILE, LOOP, REPEAT, and CASE statements, and more. Stored procedures can receive variables, return results or modify variables and return them, depending on how and where the variable is declared

There are two type of stored procedures in AS400 - External stored procedures and SQL stored procedures. While creating the external Procedures an external program( CL,RPGLE,COBOL,C etc) will linked to procedure. But in SQL stored procedures all the statements will be SQL statements. The AS400 stored procedures can also be called from .NET,JAVA,VB etc other front end applications.

External Stored Procedure


In the below example an external procedure is created with CREATE PROCEDURE statement in the STRSQL utility. The program CHECKCUST( RPGLE - for checking the customer is present or not ) is linked to the procedure so that when a call comes to the procedure, indirectly procedure invokes the program with passed parameters. Below program (CHECKCUST) returns a flag(RECFND) which procedure passes to the calling program PROTESTPGM (SQLRPGLE).

SOURCE : Procedure calling program (PROTESTPGM)
-----------------------------------------------

The real cases the procedures are called from the front-end applications. But we can also call the procedures from SQLRPGLE in AS400 itself.

SOURCE: CHECKCUST ( RPGLE Program attached to Procedure)
-------------------------------------------------------

CREATE PROCEDURE :


The below SQL statment includes the language of the external program,External program Name/Library. For more Stored Procedures

CREATE PROCEDURE DAVNAV1/PROCCHECKCUST(IN CUSTNUM DECIMAL (5,0), OUT
RECFOUND CHAR (1 )) LANGUAGE RPGLE DETERMINISTIC NO SQL EXTERNAL
NAME DAVNAV1/CHECKCUST PARAMETER STYLE GENERAL
Procedure PROCCHECKCUST was created in DAVNAV1.

For verifing:

SELECT * FROM SYSROUTINES or SELECT * FROM SYSPARAMS , execute the SQL and see the SCHEMA entry in the tables.

SQL Stored Procedures


SQL stored procedures are written in SQL language and this can be compiled or created using CREATE PROCEDURE or RUNSQLSTM command or iSeries Navigator

SQL SOURCE(DELETCUST):

Below example SQL source is for creating a procedure to delete the specific Customer Record.
http://publib.boulder.ibm.com/html/as400/v4r5/ic2931/info/db2/rbafymst151.htm#HDRSQLPROC

Compilation: The sql source can be compiled using the Command RUNSQLSTM


RUNSQLSTM SRCFILE(DAVNAV1/QSQLSRC) SRCMBR(DELETCUST) COMMIT(*NC) DBGVIEW(*SOURCE)

COMMIT(*NC) --> Specifies that commitment control is not used. Uncommitted changes in other jobs can be seen. If the SQL DROP SCHEMA
statement is included in the program, *NONE or *NC must be used.

DBGVIEW(*SOURCE) --> debugging with source.

CALLING SQLRPGLE PROGRAM:
--------------------------------------