Parameter style SQL

With parameter style SQL, the parameters are passed into the external program as follows:

SQL-parameter

This argument is set by Db2 before calling the procedure. This value repeats n times, where n is the number of parameters specified in the procedure definition. The value of each of these parameters is taken from the expression specified in the CALL statement. It is expressed in the data type of the defined parameter in the CREATE PROCEDURE statement. Note: Changes to any parameters that are defined as INPUT will be ignored by Db2 upon return.

SQL-parameter-ind

This argument is set by Db2 before calling the procedure. It can be used by the procedure to determine if the corresponding SQL-parameter is null or not. The nth SQL-parameter-ind corresponds to the nth SQL-parameter, described previously. Each indicator is defined as a two-byte signed integer. It is set to one of the following values:

0

The parameter is present and not null.

-1

The parameter is null.

Note: Changes to any indicators that correspond to INPUT parameters are ignored by Db2 upon return.

SQL-state

This argument is a CHAR(5) value that represents the SQLSTATE.

This parameter is passed in from the database set to '00000' and can be set by the procedure as a result state for the procedure. While normally the SQLSTATE is not set by the procedure, it can be used to signal an error or warning to the database as follows:

01Hxx

The procedure code detected a warning situation. This results in an SQL warning. Here xx may be one of several possible strings.

38xxx

The procedure code detected an error situation. It results in a SQL error. Here xxx may be one of several possible strings.

procedure-name

This argument is set by Db2 before calling the procedure. It is a VARCHAR(139) value that contains the name of the procedure on whose behalf the procedure code is being called.

The form of the procedure name that is passed is:

     <schema-name>.<procedure-name>

This parameter is useful when the procedure code is being used by multiple procedure definitions so that the code can distinguish which definition is being called. Note: This parameter is treated as input only; any changes to the parameter value made by the procedure are ignored by Db2.

specific-name

This argument is set by Db2 before calling the procedure. It is a VARCHAR(128) value that contains the specific name of the procedure on whose behalf the procedure code is being called.

Like procedure-name, this parameter is useful when the procedure code is being used by multiple procedure definitions so that the code can distinguish which definition is being called. Note: This parameter is treated as input only; any changes to the parameter value made by the procedure are ignored by Db2.

diagnostic-message

This argument is set by Db2 before calling the procedure. It is a VARCHAR(70) value that can be used by the procedure to send message text back when an SQLSTATE warning or error is signaled by the procedure.

It is initialized by the database on input to the procedure and may be set by the procedure with descriptive information. Message text is ignored by Db2 unless the SQL-state parameter is set by the procedure.

dbinfo

This argument is set by Db2 before calling the procedure. It is only present if the CREATE PROCEDURE statement for the procedure specifies the DBINFO keyword. The argument is a structure whose definition is contained in the sqludf include.