Parameter style SQL
The SQL parameter style conforms to the industry standard SQL.
- 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.