Administration and Programming Guide for OS/400

Stored Procedures

A stored procedure is a compiled program stored in DB2 that can execute SQL statements. In Net.Data, stored procedures are called from Net.Data functions using a CALL statement. Stored procedure parameters are passed in from the Net.Data function parameter list. You can use stored procedures to improve performance and integrity by keeping compiled SQL statements with the database server. Net.Data supports the use of stored procedures with DB2 through the SQL and ODBC language environments.

This section describes following topics:

• Stored Procedure Syntax
• Calling a Stored Procedure
• Passing Parameters
• Processing Result Sets

Stored Procedure Syntax

The syntax of the stored procedure uses the FUNCTION statement, the CALL statement, and optionally a REPORT block.

%FUNCTION  function_name ([IN datatype arg1, INOUT datatype arg2, 
    OUT tablename, ...]) {
  CALL stored_procedure 
[%REPORT [(resultsetname)] { %}]
...
[%REPORT [(resultsetname)] { %}]
[%MESSAGE %}]
 
%}

Where:

function_name

Is the name of the Net.Data function that initiates the call of the stored procedure

stored_procedure

Is the name of the stored procedure

datatype

Is one of the database data types supported by Net.Data as shown in Table 6. The data types specified in the parameter list must match the data types in the stored procedure. See your database documentation for more information about these data types.

tablename

Is the name of a Net.Data table in which the result set is to be stored (used only when the result set is to be stored in a Net.Data table). If specified, this parameter name must match the associated parameter name for resultsetname.

resultsetname

Is the name that associates a result returned from a stored procedure with a REPORT block and a table name on the function parm list, or both. The resultsetname on a REPORT block must match a tablename on the function parameter list.

Table 6. Stored Procedures Data Types

Calling a Stored Procedure

1. Define a function that initiates a call to the stored procedure.

   %FUNCTION (DTW_SQL) function_name() 
   

2. Optionally, specify any IN, INOUT, or OUT parameters for the stored procedure, including the result set name of any result sets that are returned from the stored procedure. You can also specify as the table names or result sets, as IN or INOUT parameters, from another stored procedure.

   %FUNCTION (DTW_SQL) function_name (IN datatype
   arg1, INOUT datatype arg2, 
       OUT tablename...) 
   

3. Use the CALL statement to identify the stored procedure name.

    CALL stored_procedure
   

4. If the stored procedure is going to generate one result set, optionally specify a REPORT block to define how Net.Data displays the result set.

   %REPORT  {
   ...
   %}
   

   Example:

   %FUNCTION (DTW_SQL) mystoredproc (IN CHAR(30) arg1 OUT mytable)  {
        CALL myproc   
    %REPORT {
     ...
     %ROW {  ...   %}
     ...   
    %} 
   %}
   

5. If the stored procedure is going to generate more than one result set:
   • Specify the result sets as OUT parameters in the FUNCTION statement. The result sets are saved as local tables.

     %FUNCTION (DTW_SQL) function_name (OUT tablename, ...) 
     

   • Optionally specify one or more REPORT blocks to define how Net.Data displays the result sets.

     %REPORT(resultsetname1) {
     ...
     %}
     

     Example:

     %FUNCTION (DTW_SQL) mystoredproc (IN CHAR(30) arg1, OUT table1, table2) {
        CALL myproc
      %REPORT (table1) { 
           ...      
           %ROW {  ...   %}  
           ...   
      %} 
      %REPORT (table1) { 
           ...      
           %ROW {  ...   %}  
           ...   
      %} 
     %}
     

Passing Parameters

You can pass parameters to a stored procedure and you can have the stored procedure update the parameter values so that the new value is passed back to the Net.Data macro. The number and type of the parameters on the function parameter list must match the number and type defined for the stored procedure. For example, if a parameter on the parameter list defined for the stored procedure is INOUT, then the corresponding parameter on the function parameter list must be INOUT. If a parameter on the list defined for the stored procedure is of type CHAR(30), then the corresponding parameter on the function parameter list must also be CHAR(30).

Example 1: Passing a parameter value to the stored procedure

%FUNCTION (DTW_SQL) mystoredproc (IN CHAR(30) valuein)  {
    CALL myproc 
...

Example 2: Returning a value from a stored procedure

%FUNCTION (DTW_SQL) mystoredproc (OUT VARCHAR(9) retvalue)  {
  CALL myproc  
...

Processing Result Sets

You can return one or more result sets from a stored procedure. The result sets can be stored in Net.Data tables for further processing within your macro or processed using a REPORT block. If a stored procedure generates multiple result sets, you must associate a name with each result set generated by the stored procedure. This is done by specifying parameters on the FUNCTION statement. The name you specify for a result set can then be associated with a REPORT block or a Net.Data table, enabling you to determine how each result set is processed by Net.Data. You can:

• Have the result processed in Net.Data's default report style by not defining a report block for the result set.
• Associate a result set with a REPORT block to apply your own report style. In the REPORT block, you can use Net.Data variables, text processing statements like HTML or JavaScript, or other functions to specify how the report data is displayed in the browser.

Result sets are always stored in local tables so that another function in the macro can also access the data. For example, you can pass a Net.Data table to another function so that it can use the data for calculations and display the results based on those calculations.

See Guidelines and Restrictions for Multiple REPORT Blocks for guidelines and restrictions when using multiple report blocks.

To return a single result set and use default reporting:

Use the following syntax:

%FUNCTION (DTW_SQL) function_name (OUT tablename) {
    CALL stored_procedure 
%}

For example:

 %FUNCTION (DTW_SQL) mystoredproc(OUT mytable1) {
    CALL myproc 
%}

To return a single result set and specify a REPORT block:

Use the following syntax:

%FUNCTION (DTW_SQL) function_name (OUT tablename) {    
     CALL stored_procedure  [(resultsetname)]
 %REPORT [(resultsetname)] {
  ...
 %}
%}

For example:

%FUNCTION (DTW_SQL) mystoredproc (OUT mytable1) {
     CALL  myproc   
 %REPORT {
      ...
      %ROW {  ...   %}
      ...  
 %} 
%}

Alternatively, the following syntax can be used:

%FUNCTION (DTW_SQL) function_name () {
    CALL stored_procedure  
 
 %REPORT () {
  ...
 %}
%}

For example:

%FUNCTION (DTW_SQL) mystoredproc () {
    CALL myproc   
 %REPORT  {
  ...      
  %ROW {  ...   %}
  ...  
 %} 
%}

To return multiple result sets and display them using default report formatting:

Use the following syntax:

%FUNCTION (DTW_SQL) function_name (OUT tablename1, tablename2) {
    CALL stored_procedure 
%}

Where no report block is specified.

For example:

%DEFINE DTW_DEFAULT_REPORT = "YES"
%FUNCTION (DTW_SQL) mystoredproc (OUT mytable1, mytable2) {
    CALL myproc
%}

To return multiple result sets and specify REPORT blocks for display processing:

Each result set is associated with its one or more REPORT blocks. Use the following syntax:

%FUNCTION (DTW_SQL) function_name (OUT tablename1, tablename2, ...) { 
   CALL stored_procedure 
 %REPORT (tablename1) 
   ...      
   %ROW {  ...   %}   
   ...   
 %}    
 %REPORT (tablename2)       
   ...      
   %ROW {  ...   %} 
   ...   
 %} 
 
...
%}

For example:

%FUNCTION (DTW_SQL) mystoredproc (OUT mytable1, mytable2) {  
   CALL myproc    
 
 %REPORT(mytable1) {     
  ...    
  %ROW {  ...   %}     
  ...  
 %}   
 
 %REPORT(mytable2) {     
  ...     
  %ROW {  ...   %}     
  ...  
 %} 
%}