SQL stored procedure in IBM i

SQL stored procedure is a program to perform specific function in IBM i.

SQL Stored Procedure in IBM i

What is SQL stored procedure in IBM i?

The stored procedure in IBM i is the type of program which is written to perform a specific operation.

The store procedure can be an external stored procedure or SQL stored procedure.

This article will explain about SQL stored procedure in IBM i.

Now, through a simple example, I will try to explain the basic SQL stored procedure in IBM i creation.

Example of SQL store procedure in IBM i

in this example, we will try to update a table through the SQL stored procedure.

Table structure

Field               File               Type                 Length
NAME                PF8_D              CHARACTER                20
GENDER              PF8_D              CHARACTER                 1

SQL stored procedure structure

CREATE OR REPLACE PROCEDURE SQL_Stored_Procedure_In_IBMi(         
                                               IN P_NAME CHAR(20),                              
                                               IN P_GENDER CHAR(1)                              
                                              )                                               
SPECIFIC STORED1                                         
BEGIN                                                    
  INSERT INTO PF8_D(NAME,GENDER) VALUES(P_NAME,P_GENDER);
END

'Create Procedure' or 'Create or Replace Procedure' can be used to create the SQL stored procedure in IBM i. The only difference is that in 'Create or Replace Procedure' if the SQL stored procedure already exists on the system then it will get replaced by this.
With 'IN' the SQL stored procedure will accept the input parameters.
'SPECIFIC' is used to give the SQL stored procedure a system name.
And, the 'BEGIN/END' segment operation has to be defined that needs to be performed when this SQL stored procedure is called.
RUNSQLSTM command will be used to compile the SQL stored procedure.

DECLARE And SET Statement In SQL Stored Procedure In IBM i

DECLARE

Declare is used in the SQL stored procedure to define the variables and their default values.

Declare can be used in multiple ways in the SQL stored procedure

DECLARE Variable Name INT DEFAULT Dafault Value() or NULL);

When declaring multiple variables

DECLARE Variable Name1,Variable Name2 INT

SET

Set is used in the SQL stored procedure to define values to the variables defined.

Values to the variables can be defined in multiple ways

SET Varibale Name = Value

SET Varibale Name = NULL

SET Varibale Name = SQL Query

SET Varibale Name = Calculation

SET SET Varibale Name = Value, Varibale Name2 = Value

SET Varibale Name1 = Varibale Name2 = Value

Below is the example of SQL stored procedure in IBM i with DECLARE and SET statement.

CREATE OR REPLACE PROCEDURE SQL stored procedure in IBM i()         
SPECIFIC SQL stored procedure in IBM i short name                              
BEGIN                                         
  DECLARE VAR1 INT DEFAULT 0;                 
  DECLARE VAR2,VAR3 INT;                      
  SET VAR1 = 1;                               
  SET VAR1 = NULL;                            
  SET VAR1 = (SELECT COUNT(NAME) FROM File Name); 
  SET VAR1 = 5 + 2;                           
  SET VAR2 = 10, VAR3 = 9;                    
  SET VAR2 = VAR3 + 9;                        
END

INTO Statement In SQL Stored Procedure In IBM i

INTO

Into statement is used for the movement of the values to the variables.

The movement of value and variable can be done in multiple ways.

VALUES(Value) INTO Variable Name

VALUES(NULL) INTO Variable Name

SELECT Single selected value INTO Variable Name FROM File Name

VALUES(Value1 , Value2) INTO Variable Name1, Variable Name2

SELECT First selected value, Second selected value INTO Variable Name1, Variable2

Below is the example of SQL stored procedure in IBM i with INTO statement.

CREATE OR REPLACE PROCEDURE SQL stored procedure in IBM i()                     
SPECIFIC SQL stored procedure in IBM i short name                                          
BEGIN                                                     
  DECLARE VAR1 INT DEFAULT 0;                             
  DECLARE VAR2 INT DEFAULT 0;                             
  DECLARE VAR3 INT DEFAULT 0;                             
  DECLARE VAR4 INT DEFAULT 0;                             
  VALUES(VAR1 + 2) INTO VAR1;                             
  VALUES(NULL) INTO VAR2;                                 
  SELECT COUNT(NAME) INTO VAR3 FROM File Name;                
  VALUES(VAR1 + 2, VAR2+9) INTO VAR1,VAR2;                
  SELECT COUNT(NAME), COUNT(*) INTO VAR3,VAR4 FROM File Name; 
END

SELECT And INTO in Dynamic SQL Query inside SQL Stored Procedure in IBM i

Below is the example of SQL stored procedure in IBM i with Select and Into with dynamic sql query.

CREATE OR REPLACE PROCEDURE SQL stored procedure in IBM i  ()                        
SPECIFIC SQL stored procedure in IBM i short name                                             
BEGIN                                                        
  DECLARE VAR1 VARCHAR(1000);                                
  DECLARE VAR2 INT;                                          
  SET VAR1 = 'VALUES(SELECT COUNT(NAME) FROM PF8_D) INTO ?'; 
  PREPARE COUNTRECORDS FROM VAR1;                            
  EXECUTE COUNTRECORDS USING VAR2;                           
END

In the above example below code is used to set the dynamic SQL query in VAR1

SET VAR1 = 'VALUES(SELECT COUNT(NAME) FROM PF8_D) INTO ?';

Further on which SQL statement will be prepared as below code.

PREPARE COUNTRECORDS FROM VAR1;

And after the SQL statement preparation, it will be executed as below

EXECUTE COUNTRECORDS USING VAR2;

Where the result will be stored in the executing statement variable as in this case is VAR2.

Debug the SQL Store Procedure in IBM i

SQL Store Procedure in IBM i is a CLE program so it can also be debugged as a simple CLE program. the only difference will that while executing the RUNSQLSTM command for creating the SQL Stores Procedure the parameter Debugging view should be *SOURCE.

                        Run SQL Statements (RUNSQLSTM)                        
                                                                              
Type choices, press Enter.                                                    
                                                                              
SQL rules  . . . . . . . . . . .   *DB2          *DB2, *STD                   
Decimal result options:                                                       
  Maximum precision  . . . . . .   31            31, 63                       
  Maximum scale  . . . . . . . .   31            0-63                         
  Minimum divide scale . . . . .   0             0-9                          
Concurrent access resolution . .   *DFT          *DFT, *CURCMT, *WAIT         
System time sensitive  . . . . .   *YES          *YES, *NO                    
Listing output . . . . . . . . .   *NONE         *NONE, *ERROR, *NOLIST...    
Target release . . . . . . . . .   *CURRENT      *CURRENT, VxRxMx             
 Debugging view . . . . . . . . . > *SOURCE       *NONE, *SOURCE, *STMT, *LIST 
Close SQL cursor . . . . . . . .   *ENDMOD       *ENDMOD, *ENDACTGRP          
Delay PREPARE  . . . . . . . . .   *NO           *NO, *YES                    
User profile . . . . . . . . . .   *NAMING       *NAMING, *USER, *OWNER       
Dynamic user profile . . . . . .   *USER         *USER, *OWNER

SYSPROCS And SYSPARMS Table In SQL Stored Procedure In IBM i

We can verify the creation of the SQL stored procedure in IBMi from SYSPROCS table in QSYS2 library.

select * from qsys2.sysprocs where routine_name=SQL_Strored_Procedure_In_IBM i

To the details of parameters in the SQL stored procedure in IBM i.

select * from qsys2.sysparms where specific_name= SQL_Strored_Procedure_In_IBM i system name

To execute the SQL stored procedure in IBM i , we can use STRSQL from the command line or through any of the SQL statements in the CLLE or SQLRPGLE programs.

CALL SQL_STORED_PROCEDURE_IN_IBMi(VALUE1, VALUE2)

Did you find the information listed in this article helpful? If you enjoyed this article, share it with your friends and colleagues!

Thanks!

AS400 and SQL Tricks